如何避免使用Cursor实现这个伪代码 - SQL Server [英] How Can I avoid using Cursor for implementing this pseudo code - SQL Server

问题描述

CREATE PROCEDURE p_processDataFor @accountId
BEGIN

    for each item in 
      (select * from Accounts where accountId   = @accountId and isProcessed = 0)
    BEGIN
        CASE current row
            WHEN  has x Condition THEN
                exec p_x <Pass all data of current row>
            WHEN  has y Condition THEN
                exec p_y <Pass all data of current row>
            WHEN  has z Condition THEN
                exec p_z <Pass all data of current row>
            END
    END

END

推荐答案

好的，这个例子只有条件X的插入，但希望能显示你可以继续的方式：

Okay, this example only does the insert for condition X, but hopefully shows you the way you could proceed:

create table T1 (
    ID int IDENTITY(1,1) not null,
    Val1 varchar(10) not null,
    constraint PK_T1 PRIMARY KEY (ID)
)
go
create table T2 (
    ID int not null,
    Val2 varchar(10) not null,
    constraint PK_T2 PRIMARY KEY (ID)
)
go
create table Val (
    ID int IDENTITY(1,1) not null,
    Val1 varchar(10) not null,
    Val2 varchar(10) not null,
    Processed bit not null,
    CondX bit not null
)
go

Val是我的表，包含要处理的行（在您的示例中为Accounts）。 T1和T2是当前通过 p_x 过程插入/更新的两个表。

Val is my table containing rows to be dealt with (in your example, Accounts). T1 and T2 are two tables that are currently inserted into/updated by your p_x procedure.

insert into Val(Val1,Val2,Processed,CondX)
select 'abc','def',0,1 union all
select 'ghi','jkl',0,0 union all
select 'mno','pqr',0,1
go

只是一些示例数据 - 我有3行，其中2行匹配条件x：

Just some sample data - I've got 3 rows, 2 of which match "condition x":

declare @Inter table (ValID int,T1ID int,Val2 varchar(10))

;merge into T1 using (select * from Val where CondX=1) Val on 1=0
when not matched then insert (Val1) values (Val.Val1)
output inserted.ID,Val.ID,Val.Val2 into @Inter (T1ID,ValID,Val2);

insert into T2(ID,Val2)
select T1ID,Val2 from @Inter

update Val set Processed = 1 where ID in (select ValID from @Inter)
go

对于你的实际工作，一个用于x，y和z中的每一个。如果它在同一个存储过程中，你需要为@Inter表使用不同的名称。 合并声明略有滥用，因为您无法使用 OUTPUT子句，用于引用insert语句中的其他表。但我们使用它来捕获从T1生成的IDENTITY值，以及将插入到其他表中的相应数据。

For your actual work, you'd want 3 copies of the above - one for each of x, y and z. If it's inside the same stored proc, you'd need to use a different name for the @Inter table. The merge statement is being slightly abused, because you can't use an OUTPUT clause that references other tables from an insert statement. But we're using that in order to capture the generated IDENTITY values from T1, along with the corresponding data that's going to be inserted into other tables.

现在我们将使用表变量 @Inter 进一步插入T2，并最终更新Val以指示行已被处理。如果有一个链表，你需要插入和获取标识值，你需要引入更多的合并语句和表变量。

So now we'll use the table variable @Inter for a further insert into T2, and to eventually update Val to indicate that the rows have been processed. If there's a chain of tables where you need to insert and grab identity values, you'd need to introduce more merge statements and table variables.

select * from Val
select * from T1
select * from T2

我们得到我们的结果：

ID          Val1       Val2       Processed CondX
----------- ---------- ---------- --------- -----
1           abc        def        1         1
2           ghi        jkl        0         0
3           mno        pqr        1         1

(3 row(s) affected)

ID          Val1
----------- ----------
1           abc
2           mno

(2 row(s) affected)

ID          Val2
----------- ----------
1           def
2           pqr

(2 row(s) affected)

所以我们执行了我们对条件X的所有工作，

So we've performed all of our work for condition X, keeping the code set based throughout.

这篇关于如何避免使用Cursor实现这个伪代码 - SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！