Thursday, January 13, 2011

Get Identity Value when Using Dynamic SQL

If you need to get an identity value when using dynamic sql use sp_executesql and an output parameter to accomplish this.  You're identity value ends up in the @identity variable.
declare
 @sqlstatement nvarchar(4000)
,@identity int
...
set @sqlstatement = '
insert into lp.' + @permittype +'PermitNumber
(dummy) values (''dummy'') select @identity = scope_identity()';

EXECUTE sp_executesql @sqlstatement, N'@identity INTEGER OUTPUT', @identity OUTPUT

This is a great article on dynamic sql and using variables:
http://www.kodyaz.com/articles/tsql-sp_executesql-with-output-parameters.aspx
and
http://www.sommarskog.se/dynamic_sql.html

No comments:

Post a Comment