Scalar Variable Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Scalar Variable Error

Author

Topic

Mallen
Starting Member

27 Posts

Posted - 2010-05-19 : 14:58:46

I am getting the ERROR:
Msg 137, Level 15, State 2, Procedure CollectorAccountMove, Line 60
Must declare the scalar variable "@BunchLookup".
It flags the @BunchLookup in the Update on the SET line.
Am I using the wrong syntax for this? What is this error?

-------
UPDATE Collector_Move_Table
SET Collector_Move_Table.Exception = 0
FROM Collector_Move_Table
WHERE Collector_Move_Table.Queue Like 'Dia%' OR Collector_Move_Table.Queue Like 'Ski%';

DECLARE @BunchLookup Table
(
AccountID int,
BunchID int,
UserDataValue varchar(50),
BunchDesc varchar(50)
)

INSERT INTO @BunchLookup
SELECT
dbo.Account.AccountID,
dbo.Bunch.BunchID,
dbo.UserData.Value,
dbo.Bunch.Description
FROM dbo.Bunch INNER JOIN (dbo.UserData INNER JOIN dbo.Account ON dbo.UserData.AccountID = dbo.Account.AccountID) ON dbo.UserData.Value = dbo.Bunch.Description;

UPDATE dbo.Account
SET dbo.Account.QueueID = @BunchLookup.BunchID
FROM dbo.Collector_Move_Table INNER JOIN (dbo.Account INNER JOIN @BunchLookup ON dbo.Account.AccountID = @AccountID)
ON dbo.Account.Balance = dbo.Collector_Move_Table.Balance
WHERE Collector_Move_Table.Exception= 0 AND @BunchLookup.AccountID = dbo.Collector_Move_Table.AccountID;
------

Thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-19 : 15:07:35

You'll need to use an alias in the UPDATE statement for @BunchLookup.

Here's an example:
UPDATE a
SET Column1 = b.ColumnZ
FROM Table1 a
JOIN @TableVariable b
ON a.ColumnA = b.ColumnA

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-19 : 16:26:50

Also your JOINS look a bit messed up...Try this..

UPDATE a 
SET a.QueueID = b.BunchID 
FROM dbo.Account a
INNER JOIN dbo.Collector_Move_Table c on a.Balance = c.Balance
INNER JOIN @BunchLookup b on a.AccountID = b.AccountID
WHERE c.Exception= 0

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-19 : 16:35:03

Yeah the JOINs are messed up a bit. I was pressed for time to fix the original query, so I gave up and just posted an example.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources