Dynamic Cursor with sp_executesql
Recently I learned about quite useful sp_executesql stored procedure, check it out if you never used it. Using it you can execute dynamically built SQL statement with embedded parameters, including output parameters. For example, here you open cursor on dynamically built sql statement, like we open recordset in Access (before I used temporary tables for this):
DECLARE @Stock CURSOR, @sql varchar(1000), @Source varchar(1000)
Set @Source='Select ID From MyTable WHERE ' + @WhereString
set @sql=N'SET @Stock = CURSOR STATIC FORWARD_ONLY FOR ' + @Source + ';OPEN @Stock'
EXEC sp_executesql @sql, N'@Stock cursor OUTPUT', @Stock OUTPUT
FETCH NEXT FROM @curStockBooking
…
--and here goes typical cursor operation
Labels: Code Samples, SQL Server
2 Comments:
Hey.. What is @curStockBooking
You don't declare it and it seems that would be an important part of getting this loop to work.
using FETCH NEXT FROM @Stock INTO @ID
I get the first result from my query.. so I think I am close
Yes, you are right, my typo!
las line should be:
FETCH NEXT FROM @Stock ...
Post a Comment
<< Home