I just discovered that if you are using sp_executesql, in addition to passing parameters INTO the statement you are dynamically building, you can also use output parameters. This is undocumented in SQL Server 7.0 but it works. I'm probably not the first person to figure this out, but I thought I'd post anyway, sorry for the spam if this is common knowledge.
This is how you do it:
this will output a nine, not a three.
This is how you do it:
Code:
declare @y int
set @y = 3
exec sp_executesql N'set @yy = 9', N'@yy int output', @y output
print @y
this will output a nine, not a three.