How to resolve Must declare the scalar variable "@variable"

Question

There are a lot of questions on this error but I couldn't figure out why I am still getting this error. I've declared the variable but it is still complaining about Must declare the scalar variable "@tranId".;

This is my SP.

ALTER PROCEDURE "ESG"."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); 
DELETE FROM ESG.DOCUMENT_SET_TRANS_MDATA WHERE DOCUMENT_SET_TRANS_MDATA.DOCUMENT_SET_TRANSACTION_ID IN (@tranId);

END

@tranId variable has been declared already then what it is complaining about?

You have declared @tranId to be a table. On the delete statement you are not selecting the column from the table (i.e. ..WHERE TRANSACTION_ID IN (SELECT ids FROM @tranId)). — LAT, Nov 08 '17 at 19:23
Because @tranId contains ids that I need to perform delete operation upon. Please elaborate why/where do I need the second variable? — David, Nov 08 '17 at 19:24

score 2 · Accepted Answer · edited Nov 08 '17 at 19:28

2

You have declared @tranId to be a table. On the delete statement you are not selecting the column from the table (i.e. .. WHERE TRANSACTION_ID IN (SELECT ids FROM @tranId)).

edited Nov 08 '17 at 19:28

Juan Carlos Oropeza

47,252
12
78
118

answered Nov 08 '17 at 19:26

LAT

182
1
2
6

https://stackoverflow.com/questions/24929/difference-between-exists-and-in-in-sql – Matt Nov 08 '17 at 22:00

score 2 · Answer 2 · answered Nov 08 '17 at 19:35

2

Why all the table variables here? You can greatly simplify this and just do a delete.

ALTER PROCEDURE ESG.ADD_TEST_DATA
AS
BEGIN

    DELETE FROM ESG.DOCUMENT_SET_TRANS_MDATA 
    WHERE DOCUMENT_SET_TRANS_MDATA.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'
        )
    );

END

answered Nov 08 '17 at 19:35

Sean Lange

33,028
3
25
40

I could do that but I need to perform some more operations due to which I had to declare all those variables. I am not done yet with this script. – David Nov 08 '17 at 21:08
OK. Couldn't tell that from the post because the entire procedure was posted. :) – Sean Lange Nov 08 '17 at 21:11

How to resolve Must declare the scalar variable "@variable"

2 Answers2