There is however a way to use locally-scoped cursors with dynamic SQL. Anthony Faull pointed out to me that you can achieve this with cursor variables, as in this example:
1 2 3 4 5 6 7 8
|
DECLARE @my_cur CURSOR
EXEC sp_executesql
N'SET @my_cur = CURSOR STATIC FOR
SELECT name FROM dbo.sysobjects;
OPEN @my_cur',
N'@my_cur cursor OUTPUT', @my_cur OUTPUT
FETCH NEXT FROM @my_cur |
You refer to a cursor variable, just like named cursors, but there is an @ in front, and, as you see from the example, you can pass them as a parameters. (I have to confess I have never seen any use for cursor variables until Anthony Faull was kind to send me this example.)
Partager