cursor with sp_executesql and parameters

Question

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 I wanted so I decided to start from scratch and write my own.

As previously stated I have a table with a number of sql statements in it and I want to run each in turn and update the same table with the result as well as the time that I ran the code. My code so far runs through each SQL statement and executes it but I cant find either a way to update the Last_Result with the execution of each of the individual select statements i.e. ouput the value to a parameter e.g. @retval OUTPUT or even how to store it in a temp table with the ID so I can run a separate update later.
Knowing how to use @retval OUTPUT to set a parameter as well as how to update the table directly would be good from a learning perspective. Can anyone shed some light on where I am going wrong as getting desperate now.

  CREATE TABLE [dbo].[Test_Run](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](250) NULL,
    [Script] [nvarchar](max) NULL,
    [Last_Result] [nvarchar](100) NULL,
    [Last_Runtime] [datetime] NULL
CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

declare @cursor nvarchar(4000)

set @cursor = 

'declare c_tables cursor fast_forward for 

select distinct
    ID,
    Name,
    Script
from Test_Run 
order by ID asc
'

exec sp_executesql @cursor

open c_tables 

    declare @ID varchar(2),
            @Name varchar(35),
            @Scripts nvarchar(3000),
            @Result as varchar(10),
            @ParmDefinition nvarchar(500),
            @retval as varchar(10) 

SET @ParmDefinition = N'@retvalOUT int OUTPUT';

fetch next from c_tables into @ID, @Name, @Scripts

while @@fetch_status = 0 

begin 

-- insert into Test_Run(Last_Result)
 exec sp_executesql @Scripts, N'@ID',@ID;--,@ParmDefinition, @retvalOUT=@retval OUTPUT;

-- select @retval
fetch next from c_tables into @ID, @Name, @Scripts

end 

close c_tables 
deallocate c_tables 

Answer 1

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

declare 
    @id int, 
    @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 @id is not null
begin
    select @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 :-)