Sandip's Programming Zen

An attempt to share tech/coding experiences

MS SQL: Using dynamic sql in Cursor

leave a comment »

Here is sample code for using dynamic sql in cursor , you have to set two parameters @tablename and @fieldname and it will build a select query for the given field and table.

declare @sql nvarchar(1000)
declare @tablename nvarchar(50)
declare @fieldname nvarchar(50)

set @tablename = ‘MyTableName’
set @fieldname = ‘MyFieldName’

select @sql = N’set @setcodeid =  cursor for select ‘ + @fieldname + N’ from ‘ + @tablename + N’; OPEN @setcodeid’;

declare @codeid varchar(100)
declare @setcodeid cursor
exec sp_executesql @sql,N’@setcodeid cursor OUTPUT’,@setcodeid OUTPUT

fetch next
    from @setcodeid into @codeid
        while @@FETCH_STATUS = 0
                — do you stuff here…

                fetch next from @setcodeid into @codeid
close @setcodeid
deallocate @setcodeid


Written by Sandip

April 22, 2010 at 7:36 am

Posted in MS SQL

Tagged with ,

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: