Sandip's Programming Zen

An attempt to share tech/coding experiences

Posts Tagged ‘Cursor

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
            begin
                — do you stuff here…

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

Written by Sandip

April 22, 2010 at 7:36 am

Posted in MS SQL

Tagged with ,