Must declare the scalar variable "@tran_Id"

Question

I am getting an error Must declare the scalar variable "@tran_Id". while I have declared the variable and I am using the column. Am I doing anything wrong in here.

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 @tran_Id table(transaction_id INT);
    DECLARE @tranId_cursor CURSOR;
    SET @tranId_cursor = CURSOR FOR 
    SELECT * FROM @tranId;

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

    WHILE @@FETCH_STATUS = 0
    BEGIN
            DELETE FROM ESG.DOCUMENT_SET_TRANS_MDATA WHERE DOCUMENT_SET_TRANSACTION_ID IN (SELECT transaction_id FROM @tran_Id);
    END
END
CLOSE @tranId_cursor;
DEALLOCATE @tranId_cursor;

using temporary tables instead of table variables would not only be better for performance, it would make your code easier to understand as well. [When should I use a table variable vs temporary table in sql server? - Answer by Martin Smith](https://stackoverflow.com/a/13777841/2333499) — SqlZim, Nov 08 '17 at 21:42
Thus the need for good naming shows up. If you'd picked better names for things, you wouldn't have gotten confused. (`@tran_Id` AND `@tranId` in the same script?) — ErikE, Nov 08 '17 at 21:44
But in this case I am using @tran_id and transaction_id is the column — David, Nov 08 '17 at 21:46
Why are you doing this with a cursor??? You have asked more than one question today about how to do this table variables instead. — Sean Lange, Nov 08 '17 at 21:47
Because nothing is working and there are no to the point solution for it so that I can refer. — David, Nov 08 '17 at 21:50
I like sean have read your other questions it seems like you keep trying to get pieces of an approach where as the overall direction/need is probably getting lost. Because even if you use table variables or temp tables it would still not benefit you to do the operations in a cursor if you just need to delete from multiple tables. I would recommend closing some of these questions and asking 1 question with a larger scope showing all of your current attempt. Such as need to cascade delete to related tables without a foreign reference or need to do that plus insert an audit trail or....??? — Matt, Nov 08 '17 at 22:05
Echo of @SeanLange - I don't know what you are trying to accomplish here, but whatever you are doing is likely one of the least optimal approach you could have taken. Cursor in SQL = lose. — Twelfth, Nov 08 '17 at 23:16
I would suggest you post a question with ALL of the information and details about what you are trying to accomplish. You will need to post ddl and sample data along with the desired results using that sample data. I am 100% certain that if you post all the information the people here will find a solution that works. But doing this one little piece at a time is an exercise in frustration both for you and the people trying to help you. Here is a great place to start. https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Nov 09 '17 at 14:14

score 1 · Answer 1 · answered Nov 08 '17 at 21:41

1

FETCH NEXT FROM @tranId_cursor INTO @tran_Id;

This line uses @tran_Id as a scalar. It is declared as a table. QED.

answered Nov 08 '17 at 21:41

Remus Rusanu

288,378
40
442
569

2

Also the `WHILE` loop is infinite. – Remus Rusanu Nov 08 '17 at 21:42
plus no transaction_id on the `@tran_id` table variable.... and probably a couple more issues such as what is `@envelopeid` ...`SELECT identifiers FROM @envelopeId` – Matt Nov 08 '17 at 21:58

score 0 · Answer 2 · answered Nov 08 '17 at 22:21

Why not get rid of the cursor and 99% of this and just delete directly?

DELETE FROM ESG.DOCUMENT_SET_TRANS_MDATA 
WHERE DOCUMENT_SET_TRANSACTION_ID IN (SELECT t.DOCUMENT_SET_TRANSACTION_ID 
                                      FROM ESG.DOCUMENT_SET_TRANSACTION t
                                      INNER JOIN @envelopeId e on
                                      e.identifiers =  t.IDENTIFIER)

Must declare the scalar variable "@tran_Id"

2 Answers2