带有 sp_executesql 和参数的游标 [英] 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 I wanted so I decided to start from scratch and write my own.

如前所述，我有一个包含许多 sql 语句的表，我想依次运行每个语句并使用结果以及运行代码的时间更新同一个表.到目前为止，我的代码遍历每个 SQL 语句并执行它，但我找不到任何一种方法来更新 Last_Result 以执行每个单独的 select 语句，即将值输出到参数，例如@retval OUTPUT 或什至如何将其存储在带有 ID 的临时表中，以便我稍后运行单独的更新.
从学习的角度来看，知道如何使用@retval OUTPUT 设置参数以及如何直接更新表会很好.任何人都可以阐明我现在变得绝望的地方出了什么问题.

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 

推荐答案

我认为这可以解决问题(这里有一个 现场演示):

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

我完全删除了游标并改用了 while 循环 - 我想我不太喜欢游标 :-)

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

这篇关于带有 sp_executesql 和参数的游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！