CODEWITHSUNDEEP
sql-server-2008
user3071287
user3071287

Reputation: 11

When getting maxid from table name

Declare @Id bigint  
EXEC procGetMaxNumber @Id output,'employee'

I'm getting an error message

Error converting data type varchar to bigint

when I execute the procedure with the above parameters 

 -- =============================================    
    -- Description: <Fetches Max Id for a particular table>    
    -- =============================================    
CREATE PROCEDURE [dbo].[procGetMaxNumber]    
(    
  @Id BIGINT OUTPUT,  
  @TblName nvarchar(50)    

 )    
 AS    
 BEGIN    
 SET NOCOUNT ON;    

 Declare @Query nvarchar(max) = ''  
 set @Query =  'SELECT ' + @Id + ' = isnull(Max(Id),0)+ 1 from '  +   @TblName  
 --print @Query  
 Exec (@Query)  
 END

Upvotes: 0

Views: 84

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280413

This looks extremely dangerous to me. What exactly are you going to do with the "next" Id value once you get it? You know that immediately after you retrieve this value, someone could insert a row and take it (or even fail to insert a row, and still take it even if the transaction rolled back), right? If you want to reserve an Id value and be sure that is the number you get, just run the insert, don't do any of this max+1 to insert later.

Anyway you can't use EXEC to retrieve an output parameter from dynamic SQL, you'll need to use sp_executesql:

DECLARE @query NVARCHAR(MAX) = N'SELECT @Id = COALESCE(MAX(Id),0)+1
  FROM dbo.' + QUOTENAME(@TblName) + ';';

EXEC sp_executesql @query, N'@Id INT OUTPUT', @Id OUTPUT;

Upvotes: 2

Related Questions