How Can I avoid using Cursor for implementing this

问题:

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

回答1:

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 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

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

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.

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

And we get our results:

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)

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

回答2:

You cannot normally avoid looping since you are calling EXEC, which cannot be done as a SET-based operation; it has to be done one by one.

If you just want to avoid CURSOR in general, you can implement it using a WHILE loop.

Otherwise, another option is to use a SELECT + FOR XML statement which builds the EXEC statements as a single NVARCHAR(MAX) statement into a variable, then EXEC just that dynamic SQL.