From stored procedure how get max number

Question

Create Procedure [dbo].[spGenerateID]      
(             
    @sFieldName         NVARCHAR(100),
    @sTableName         NVARCHAR(100)
)          
AS     
BEGIN  
   SELECT ISNULL(MAX(ISNULL(@sFieldName, 0)), 0) + 1 FROM @sTableName 
END

In the above procedure I supply the field name and table name and I want the max number of this field .Why this not work?I also want to check if those fields are null than it's not work.. This procedure must have a return parameter of the field that I supplied which contain the max number.Please help me to fixed it.

1. Why does this not work.
2. How to check input parameter are not null.
3. How to set output parameter

Answer 1

You cannot supply the tablename and fieldname as parameters to a stored procedure.

You need to create a dynamic query and execute using sp_executesql.

You should read The Curse and Blessings of Dynamic SQL

Answer 2

If this is always to be used for identity columns you can use a variable

SELECT ISNULL(IDENT_CURRENT(@sTableName),0)+1

Otherwise you need to use dynamic SQL (The usual caveats about SQL injection apply.)

Additionally I'm somewhat dubious about the reasons behind this anyway unless you don't have any concurrency to worry about.

I've changed the type of your parameters to sysname as this is more appropriate.

CREATE PROCEDURE [dbo].[spGenerateID]      
 (             
    @sFieldName        sysname,
    @sTableName        sysname,
    @id int output
  )          
AS     
 BEGIN  
 DECLARE @dynsql NVARCHAR(1000)

 SET @dynsql = 'select  @id =isnull(max([' + @sFieldName + ']),0)+1 from [' + @sTableName + '];'
 EXEC sp_executesql  @dynsql, N'@id int output',  @id  OUTPUT

END

Example Usage

DECLARE @id int

EXECUTE [dbo].[spGenerateID] 
   'id'
  ,'MYTABLE'
  ,@id OUTPUT


SELECT  @id

Answer 3

You can't have field names and table names as parameters without wrapping the entire SELECT statement in an EXEC statement:

EXEC ('select  isnull(max(isnull([' + @sFieldName + '],0)),0)+1 
       from [' + @sTableName + '] ')

Answer 4

1) This won't work because of the way the table name was passed.

2) You only have to check for ISNULL one time, you have a redundant number of calls there.

3) You need not necessarily declare an output, just catch the return value when you execute the stored procedure.

If you're trying to generate a unique Id this is not the best way to do it because you could run into race conditions and generate a duplicate ID for one of the calls. Ideally the ID is already declared as an IDENTITY column, but if you can't do it that way then it's better to create a special table that just returns an ID as an IDENTITY column. Then you can access that table to get the latest version with assurance that you will get a unique ID.

Here is how your stored procedure could work without the redundant IsNull().

Create Procedure [dbo].[spGenerateID]                 
    @sFieldName         NVARCHAR(100),
    @sTableName         NVARCHAR(100)          

AS     

BEGIN  
Exec ( 'SELECT max(isnull(' + @sFieldName + ',0))+1 FROM ' + @sTableName)

END