AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

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

Labels: ,

2 Comments:

Anonymous Anonymous said...

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

12:01 AM  
Blogger Alex Dybenko said...

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

9:26 AM  

Post a Comment

<< Home