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 c

Solution 1:

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.

Solution 2:

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

createtable T1 (
    ID intIDENTITY(1,1) notnull,
    Val1 varchar(10) notnull,
    constraint PK_T1 PRIMARY KEY (ID)
)
go
createtable T2 (
    ID intnotnull,
    Val2 varchar(10) notnull,
    constraint PK_T2 PRIMARY KEY (ID)
)
go
createtable Val (
    ID intIDENTITY(1,1) notnull,
    Val1 varchar(10) notnull,
    Val2 varchar(10) notnull,
    Processed bit notnull,
    CondX bit notnull
)
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.

insertinto Val(Val1,Val2,Processed,CondX)
select'abc','def',0,1unionallselect'ghi','jkl',0,0unionallselect'mno','pqr',0,1
go

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

declare@Intertable (ValID int,T1ID int,Val2 varchar(10))

;mergeinto T1 using (select*from Val where CondX=1) Val on1=0whennot matched theninsert (Val1) values (Val.Val1)
output inserted.ID,Val.ID,Val.Val2 into@Inter (T1ID,ValID,Val2);

insertinto T2(ID,Val2)
select T1ID,Val2 from@Interupdate Val set Processed =1where 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        112           ghi        jkl        003           mno        pqr        11

(3row(s) affected)

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

(2row(s) affected)

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

(2row(s) affected)

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