CODEWITHSUNDEEP
sqlsql-server-2008
levkaster
levkaster

Reputation: 2730

sp_executesql return id on insert in stored procedure

I have created a dynamic stored procedure that updates,inserts and deletes from specific table:

CREATE PROCEDURE dbo.Pj_Change
@Action Varchar(20),
@TableName Varchar(20),
@InsertQuery Nvarchar(max),
@UpdateQuery Nvarchar(max),
@Where Varchar(50)

AS   
BEGIN  
  DECLARE @SQLString nvarchar(max);   

  SET NOCOUNT ON
  IF(@Action = 'UPDATE')
  SET @SQLString = 'UPDATE '+@TableName+' SET '+@UpdateQuery+' WHERE ('+@Where+')';
  ELSE IF(@Action = 'INSERT')
  SET @SQLString = 'INSERT INTO '+@TableName+' '+@InsertQuery + ';
  ELSE IF(@Action = 'DELETE')
  SET @SQLString = 'DELETE FROM '+@TableName+' WHERE ('+@Where+')';

EXECUTE sys.sp_executesql @SQLString

END

The problem is, than I try to add to this code SELECT SCOPE_IDENTITY() to get an ID of new row on insert, I get -1 in output. The change that I make is:

@SQLString = 'INSERT INTO '+@TableName+' '+@InsertQuery + '; SELECT SCOPE_IDENTITY()';

What do I need to change for this to work?

Upvotes: 0

Views: 237

Answers (1)

Blindy
Blindy

Reputation: 67380

It's ok, you shouldn't be using scope_identity anyway. You have more modern constructs, like:

update ... set ... output updated.id where ... -- same for insert and delete

You can either output the results in a table (not needed for a single insert) or use the simple form like above and have the update statement act as select and return the value.

Upvotes: 1

Related Questions