SQL CURSOR, looping on talbe's or view's rows with Cursor like Recordset or DataReader

Hi All, here an example on how loop on some rows in SQL Stored Procedure, like a visual basic RecordSet or .NET Framework DataReader, simple put your query at line 7 of script:

SET @SQL ='SELECT Field1,Field2 FROM Table_Name '

That's all, We have spent lot of time to find an example which works, finally it comes:








--LWebCode SQL Script
DECLARE @SQL NVARCHAR (4000)
DECLARE @DynamicSQL NVARCHAR(250)
DECLARE @Field1 NVARCHAR(100)
DECLARE @Field2 NVARCHAR(100)
DECLARE @outputCursor CURSOR
SET @SQL ='SELECT Field1,Field2 FROM Table_Name '
SET @DynamicSQL = 'SET @outputCursor = CURSOR FORWARD_ONLY STATIC FOR ' +
@SQL + ' ; OPEN @outputCursor'
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
FETCH NEXT FROM @outputCursor INTO @Field1,@IDField2
WHILE @@FETCH_STATUS = 0
BEGIN
/*here is loop put your code here*/
SET @SQL='SELECT * FROM t_name WHERE Field1=''' + @Field1 + ''''
EXEC SP_EXECUTESQL @SQL
FETCH NEXT FROM @outputCursor INTO @Field1,@Field2
END