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
Thursday, January 13, 2011
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment