|
ALTER PROC TestProc
AS
DECLARE @dynamicSQL nvarchar(200)
-- Have code that will construct the dynamic SQL
SET @dynamicSQL = 'SELECT FirstName FROM Contacts'
-- The cursor that will be filled by the dynamic SQL
DECLARE @outputCursor CURSOR
-- Create the dynamic SQL to fill a CURSOR instead
SET @dynamicSQL = 'SET @outputCursor = CURSOR FORWARD_ONLY STATIC FOR ' +
@dynamicSQL + ' ; OPEN @outputCursor'
-- Execute dynamic sql
exec sp_executesql -- sp_executesql will essentially create a sproc
@dynamicSQL, -- The SQL statement to execute (body of sproc)
N'@outputCursor CURSOR OUTPUT', -- The parameter list for the sproc: OUTPUT CURSOR
@outputCursor OUTPUT -- The parameter to pass to the sproc: the CURSOR
-- Code that will just output the values from the cursor
DECLARE @firstName nvarchar(200)
FETCH NEXT FROM @outputCursor INTO @firstName
-- Loop while there're more things in the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @firstName
FETCH NEXT FROM @outputCursor INTO @firstName
END
-- Be nice, close & deallocate cursor
CLOSE @outputCursor
DEALLOCATE @outputCursor |