Cursor With Sp_executesql And Parameters

I am hoping for some further help with an issue I have been struggling with, I did post a similar question yesterday but I think the example I was using was to complicated for what

Solution 1:

I think this will do the job (and here is a live demo):

declare@idint, 
    @sql nvarchar(max), 
    @last_result nvarchar(100), 
    @last_runtime datetime,
    @params nvarchar(max);

SET@params= N'@retvalOUT varchar(max) OUTPUT';


select@id=min(id) from Test_Run;
while @idisnotnullbeginselect@sql= Script from Test_Run where id =@id;
    set@sql='select @retvalOUT= ('+@sql+')';
    exec sp_executesql @sql, @params, @retvalOUT=@last_result OUTPUT;
    set@last_runtime = getdate();

    update Test_Run set Last_Result =@last_result, Last_Runtime =@last_runtime where id =@id;

    select@id=min(id) from Test_Run where id >@id;
end

I removed the cursor completely and used a while loop instead - I guess I don't like cursors that much :-)

Solution 2:

To begin with, declare your cursor normally, so there is no need to employ an sp_executesql for it:

declare c_tables cursor fast_forward forselect 
    ID,
    Name,
    Script
from Test_Run 
orderby ID asc

Notice that I removed distinct keyword, I think ID is a candidate key.

Solution 3:

The code that sp_executesql runs is in its own scope. Any variables and cursors created in that scope are not available outside.

In your case c_tables is created in the exec sql scope and thus does not exist for the open statement.