Using a table variable inside of a exists statement

Question

I am trying to update a column inside of a table variable based on a condition, the condition being that the ID of the table variable does not exist in a different table:

DECLARE @BugRep TABLE(BugCode VARCHAR(50),DevFirstName VARCHAR(50), DevLastName    VARCHAR(50), BugDate VARCHAR(20), IsValid VARCHAR(1))

UPDATE @BugRep 
SET IsValid = 'N' WHERE NOT EXISTS(SELECT * FROM BUG b WHERE @BugRep.BUGCODE = b.CODE)

When i try to compile the procedure that has these statements, I get a "Must declare the scalar variable "@BugRep" message.

How do i go about using the table variable inside of the NOT EXISTS clause?

I am using SQL Server 2008

womp · Accepted Answer · 2009-09-30 16:29:01Z

13

This will work:

[@BugRep].BUGCODE

You'll also need to change "b.CODE" to "b.BUGCODE" by the way ;)

edited Sep 30, 2009 at 16:29

answered Sep 30, 2009 at 16:20

womp

117k27 gold badges240 silver badges271 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rob Garrison · Accepted Answer · 2009-09-30 17:32:55Z

7

This is actually very picky. Check out the in-line comments below using womp's suggestion and also trying a LEFT OUTER JOIN.

CREATE TABLE Bug (CODE VARCHAR(50))

DECLARE @BugRep TABLE (
    BugCode         VARCHAR(50),
    --DevFirstName    VARCHAR(50),
    --DevLastName     VARCHAR(50),
    --BugDate         VARCHAR(20),
    IsValid         CHAR(1)
)

INSERT INTO Bug (CODE) VALUES ('Code1'), ('Code2'), ('Code3')

INSERT INTO @BugRep (BugCode) VALUES ('Code1'), ('Code2'), ('Code4')

SELECT CODE FROM Bug ORDER BY CODE
SELECT BugCode, IsValid FROM @BugRep ORDER BY BugCode

UPDATE @BugRep                          -- Can't be [@BugRep] ("Invalid object name '@BugRep'.")
SET IsValid = 'N'
WHERE NOT EXISTS (
    SELECT *
    FROM BUG b
    WHERE [@BugRep].BUGCODE = b.CODE    -- Can't be @BugRep ("Must declare the scalar variable "@BugRep".")
)

SELECT BugCode, IsValid FROM @BugRep ORDER BY BugCode

UPDATE @BugRep                          -- Can be either @BugRep or [@BugRep]
SET IsValid = 'Y'
FROM @BugRep                            -- Can't be [@BugRep] ("Invalid object name '@BugRep'.")
LEFT OUTER JOIN BUG
ON [@BugRep].BUGCODE = BUG.CODE         -- Can't be @BugRep ("Must declare the scalar variable "@BugRep".")
WHERE BUG.CODE IS NOT NULL

SELECT BugCode, IsValid FROM @BugRep ORDER BY BugCode

DROP TABLE Bug
GO

edited Sep 30, 2009 at 17:32

answered Sep 30, 2009 at 17:06

Rob Garrison

7,3144 gold badges28 silver badges23 bronze badges

2 Comments

womp Over a year ago

Man.. how annoying is that, eh? +1

Peter Radocchia Over a year ago

ah, @BugCode, used as an alias, is an invalid identifier without the brackets. of course!

Stu Pegg · Accepted Answer · 2009-09-30 19:28:40Z

1

Here's a version of the previous two using aliases to get around your issue:

UPDATE @BugRep
SET IsValid = 'N'
FROM @BugRep BR
    LEFT JOIN BUG B
        ON BR.BUGCode = B.CODE
WHERE B.CODE is null

This also avoids the inefficiencies related to "is not null" and "not exists".

answered Sep 30, 2009 at 19:28

Stu Pegg

1,3372 gold badges15 silver badges41 bronze badges

Collectives™ on Stack Overflow

Using a table variable inside of a exists statement

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related