MS SQL: Using dynamic sql in Cursor
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
