question

LudmilG-4847 avatar image
0 Votes"
LudmilG-4847 asked MelissaMa-msft commented

Wait for prefious statement to get executed in cursor

I am using a cursor and I need to wait for the @InsertTSQL to finish with the execution before I go to the Update line. How can I do that?

Cursor start

SELECT @InsertTSQL = 'INSERT INTO Test1 (SELECT * FROM Test2 WHERE id = @Iterator)';SET @ScopeIdentity = SCOPE_IDENTITY();';

             EXECUTE sp_executesql @InsertTSQL, N'@ScopeIdentity Integer OUT', @ScopeIdentity = @ScopeIdentity OUT;      

             --Update needs to wait until prev execute finish
             UPDATE Test3 Set ID = @ScopeIdentity WHEE ID = 22

Cursor ebd


sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The code you posted does not work when you insert multiple rows. You are only going to get the LAST identity field.

What exactly are you trying to do? Please post actual code.

0 Votes 0 ·

Hi @LudmilG-4847,

Could you please provide any update? Thanks.

Best regards,
Melissa

0 Votes 0 ·
TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered TomPhillips-1744 edited

SQL executes sequentially. It always waits for the previous command to finish before starting the next command. It is not possible in TSQL to make it NOT wait for the previous command to finish.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered

Hi @LudmilG-4847,

I am using a cursor and I need to wait for the @InsertTSQL to finish with the execution before I go to the Update line.

Agreed with Tom, it will not go to the update line until the execution finished. Don't worry about it.

Besides, just as a suggestion, you could also consider to use CTE to read Id columns from Inserted or Deleted tables or using Output Clause as suggested according to your tsql code in order to fetch the identity values.

Here is a sample trigger code with Output clause for reading inserted identity values. You could check whether it is a little helpful to you.

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.