Tuesday, November 16, 2010

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

at

2 comments:

  1. Anonymous12:01 AM

    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

    ReplyDelete
  2. Alex Dybenko9:26 AM

    Yes, you are right, my typo!
    las line should be:
    FETCH NEXT FROM @Stock ...

    ReplyDelete