Thanks for your help Chris and John!
I was able to make this work using the code below.
declare @objcursor as cursor
declare @delquery as nvarchar(max)
declare @vsql as nvarchar(max),@vquery as nvarchar(max)
set @vquery = 'select * from Test_A' -- 'Test_A' will be replaced by a variable from an outer cursor
set @vsql = 'set @cursor = cursor for ' + @vquery + ' open @cursor;'
exec sys.sp_executesql
@vsql
,N'@cursor cursor output'
,@objcursor output
fetch next from @objcursor
while (@@fetch_status = 0)
begin
SET @delquery = 'delete from Test_A where current of @objcursor'; -- 'Test_A' will be replaced by a variable from an outer cursor
exec sys.sp_executesql @delquery
,N'@objcursor as cursor'
,@objcursor
<some code goes here to track the progress and commit transaction based on cursor count>
fetch next from @objcursor;
end
close @objcursor
deallocate @objcursor