How to loop through a variable of type table in SQL Server?

Question

This is continuity of my first question in which I wanted to store multiple values in SQL Server variable.I need to loop through @tranId variable which holds two values and then delete a transaction based on the ids I have in @tranId.

This is how my query looks like.

ALTER PROCEDURE "ESG"."SP_ADD_TEST_DATA"
AS
BEGIN

DECLARE @envelopeId table(identifiers VARCHAR(1000));

INSERT INTO @envelopeId (identifiers) VALUES('axaa1aaa-aaaa-a5aa-aaaa-aa8aaaa9aaaa');
INSERT INTO @envelopeId (identifiers) VALUES('bxbb1bbb-bbbb-b5bb-bbb4-bb8bbbb9bbbf');

DECLARE @tranId table(ids INT);
INSERT INTO @tranId SELECT DOCUMENT_SET_TRANSACTION_ID FROM ESG.DOCUMENT_SET_TRANSACTION WHERE IDENTIFIER IN (SELECT identifiers FROM @envelopeId); 

BEGIN
    DECLARE @transactionId int;
    DECLARE @tranId_cursor CURSOR;
    SET @tranId_cursor = CURSOR FOR 
    SELECT * FROM @tranId;

    OPEN @tranId_cursor
    FETCH NEXT FROM @tranId_cursor INTO @transactionId;

    WHILE @@FETCH_STATUS = 0
    BEGIN
            DELETE FROM ESG.DOCUMENT_SET_TRANS_MDATA WHERE DOCUMENT_SET_TRANSACTION_ID = @transactionId;
    END
END
CLOSE @tranId_cursor;
DEALLOCATE @tranId_cursor;
END

This is getting complex and I am not sure how to achieve this gracefully.

Thanks

Why do you think you need to loop here? I don't understand what you are trying to do here but I think you are over complicating it. — Sean Lange, Nov 08 '17 at 15:16
Yeah, maybe you dont need a loop here. You can show your deletion code so we can find an alternative ways. And here if you still want that https://stackoverflow.com/questions/61967/is-there-a-way-to-loop-through-a-table-variable-in-tsql-without-using-a-cursor — Edward N, Nov 08 '17 at 15:20
Honestly speaking this is my first time doing it and probably I am making it harder but the reason I am traversing through this is because @tranId carries two ids 1234 and 4567. For each of them I need to perform a delete operation afterwards. — David, Nov 08 '17 at 15:21
Everything that is here should be working... we need to see more of what you have going on now and what you have planned. — Mad Myche, Nov 08 '17 at 15:22
It is working. Now I need to delete entries for the ids which are in @tranId variable. — David, Nov 08 '17 at 15:26
you don't need a loop for that. Just Join your @tranid table with the transaction table and delete the record. if you can show us your delete statement we will be able to help you out. Adding sample data to you question will help us as well. Please help us to help you. — Kashif Qureshi, Nov 08 '17 at 15:28
If I understand you want to delete them one by one!so you can use `while` over you table such as `While (Select Count(*) From @tranId) > 0 Begin declare @Id int; Select Top 1 @Id = Ids From @tranId; Delete @tranId Where ids = @Id ; End` — Aria, Nov 08 '17 at 15:37
You should avoid using double quotes around your objects names. And you should avoid the SP_ prefix. http://sqlperformance.com/2012/10/t-sql-queries/sp_prefix Best is to use no prefix at all. — Sean Lange, Nov 08 '17 at 15:46

score 0 · Answer 1 · answered Nov 08 '17 at 15:31

0

If I understand what you are asking, this is what I think you should do

Since @tranId is a table you can create a cursor as

DECLARE tran_cursor CURSOR  
FOR SELECT * FROM @tranId;

and the in a WHILE loop, get its contents using

OPEN tran_cursor FETCH NEXT FROM tran_cursor INTO @rec;

You can access the values as @rec.ids inside the delete you are going to use.

You can find more information in https://learn.microsoft.com/en-us/sql/t-sql/language-elements/while-transact-sql as well as in https://learn.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql

answered Nov 08 '17 at 15:31

Theodore Mavrikis

1
1

NO. A cursor to perform deletes like this is absurd. We want to avoid loops and cursors whenever possible. – Sean Lange Nov 08 '17 at 15:44
I've updated my query in the question and it looks like it goes to infinite loop. – David Nov 08 '17 at 16:33
@SeanLange You are right, my bad. I think my answer further complicated the issue. I saw that David wanted to loop through his table and my mind immediately thought of a loop. – Theodore Mavrikis Nov 13 '17 at 15:51

Peter B · Answer 2 · 2017-11-08T17:46:06.800

0

You can use a DELETE statement that directly uses the first table variable, like this:

DELETE ESG.DOCUMENT_SET_TRANS_MDATA
WHERE DOCUMENT_SET_TRANSACTION_ID IN
   (SELECT DOCUMENT_SET_TRANSACTION_ID
    FROM ESG.DOCUMENT_SET_TRANSACTION
    WHERE IDENTIFIER IN
       (SELECT identifiers FROM @envelopeId)
   )

As an alternative it can also be done without using any table variables:

DELETE ESG.DOCUMENT_SET_TRANS_MDATA
WHERE DOCUMENT_SET_TRANSACTION_ID IN
   (SELECT DOCUMENT_SET_TRANSACTION_ID
    FROM ESG.DOCUMENT_SET_TRANSACTION
    WHERE IDENTIFIER IN
       ('axaa1aaa-aaaa-a5aa-aaaa-aa8aaaa9aaaa',
        'bxbb1bbb-bbbb-b5bb-bbb4-bb8bbbb9bbbf'
       )
   )

edited Nov 08 '17 at 17:46

answered Nov 08 '17 at 15:38

Peter B

22,460
5
32
69

I need to delete the entries based on the ids Peter, therefore I am going through all this complex SP. – David Nov 08 '17 at 16:39
I updated my answer to delete from the same table as in your revised code sample, still without a cursor and with just 1 or no helper table variables. This approach makes SQL do the 'looping' which performs much faster that using a CURSOR. – Peter B Nov 08 '17 at 17:49

How to loop through a variable of type table in SQL Server?

2 Answers2