Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using a table variable inside of a exists statement

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

like image 925
Developer Avatar asked Sep 30 '09 16:09

Developer


People also ask

Can we use table variable in dynamic query?

You can use a table variable with dynamic SQL, but you must declare the table inside the dynamic SQL itself.

Can we use table variable in function in SQL Server?

Functions and variables can be declared to be of type table. table variables can be used in functions, stored procedures, and batches. To declare variables of type table, use DECLARE @local_variable. Applies to: SQL Server (SQL Server 2008 and later), Azure SQL Database.

How do I SELECT a value from a table to a variable in SQL?

The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving.

How do you DECLARE a table variable in SQL?

To declare a table variable, start the DECLARE statement. The name of table variable must start with at(@) sign. The TABLE keyword defines that used variable is a table variable. After the TABLE keyword, define column names and datatypes of the table variable in SQL Server.


3 Answers

This will work:

[@BugRep].BUGCODE

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

like image 51
womp Avatar answered Oct 07 '22 21:10

womp


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
like image 37
Rob Garrison Avatar answered Oct 07 '22 21:10

Rob Garrison


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".

like image 36
Stu Pegg Avatar answered Oct 07 '22 22:10

Stu Pegg