DECLARE cursor1 CURSOR LOCAL FOR
SELECT ORDER_ID
FROM @TableName1
OPEN cursor1
FETCH next FROM cursor1 INTO @ORDER_ID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Hi'
FETCH NEXT FROM cursor1 INTO @ORDER_ID
END
CLOSE cursor1
DEALLOCATE cursor1
How to use dynamic table name in SELECT query to define cursor?
@TableName1 is a VARCHAR variable which stores a table name.
You can copy the records of interest into a table variable / temporary table and then declare the cursor over that table:
declare @t table (
OrderId int
);
declare @OrderId int, @Sql nvarchar(max);
set @Sql = N'select ORDER_ID from ' + @TableName1;
insert into @t (OrderId)
exec (@Sql);
DECLARE cursor1 CURSOR LOCAL fast_forward FOR
SELECT OrderId from @t;
OPEN cursor1;
WHILE 1=1 BEGIN
FETCH NEXT FROM cursor1 INTO @ORDER_ID;
if @@fetch_status != 0
break;
PRINT 'Hi';
END
CLOSE cursor1;
DEALLOCATE cursor1;
declare @TableName1 as varchar(256)
set @TableName1 = 'TableName'
declare @sql as nvarchar(max)
set @sql = 'DECLARE cursor1 CURSOR GLOBAL FOR SELECT ORDER_ID FROM ' + cast(@TableName1 as nvarchar(256))
exec(@sql)
OPEN cursor1
FETCH next FROM cursor1 INTO @ORDER_ID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'ORDER_ID=' + cast(@ORDER_ID as varchar(256))
FETCH NEXT FROM cursor1 INTO @ORDER_ID
END
CLOSE cursor1
DEALLOCATE cursor1
exec() run in separate context. The LOCAL CURSOR is not recognizalbe by OPEN cursor in the rest of the code. You need to use CURSOR GLOBAL for exec() to work.You can assign a cursor to a dynamic/variable table by using sp_executesql and output-ing an open cursor. Cursor names can also be variable.
declare @TableName1 varchar(50) = 'TableName';
--declare @sql nvarchar(max)= concat('set @mycursor = cursor for select * from ', QUOTENAME(@TableName1), '; open @mycursor;');
declare @sql nvarchar(max) = 'set @dynamicsqlcursor = cursor for select name from sys.objects; open @dynamicsqlcursor;';
declare @mycursor cursor,
@objname sysname;
exec sp_executesql @stmt = @sql, @params = N'@dynamicsqlcursor cursor output', @dynamicsqlcursor = @mycursor output; --the output cursor must be open
--no need to open the cursor, it is output "opened"
--open @mycursor;
fetch next from @mycursor into @objname;
while @@fetch_status = 0
begin
print 'object name=' + @objname;
fetch next from @mycursor into @objname;
end
close @mycursor;
deallocate @mycursor;
