Table Variables - Can you join them? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Table Variables - Can you join them?

Author

Topic

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2004-05-07 : 09:18:16

Hey all, I have created a couple of table variables which are working great. However I need to do an inner join on them, but keep getting an error.

"Server: Msg 137, Level 15, State 2, Line 324
Must declare the variable '@PRODUCTION'."

One of my table variables is @PRODUCTION.

Here is my join code.
SELECT P_WORKCENTER, P_DATE, P_LOT, P_PROJECT, GROSSQTY, PT_DESC, PT_QTY
FROM @PRODUCTION INNER JOIN @PRODUCTION_TOTALS ON @PRODUCTION.P_NUM = @PRODUCTION_TOTALS.P_NUM;

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-05-07 : 09:56:54

You might have batched the query off using GO.
If you did this you would have lost everything declared before the go statement.

--******************* This works:

declare @TableA TABLE(Col1 int, Col2 int)
declare @TableB Table(Col1 int, Col2 int)

INSERT INTO @TableA Values(1, 1)
INSERT INTO @TableA Values(111, 2)
INSERT INTO @TableA Values(12, 3)
INSERT INTO @TableA Values(13, 4)
INSERT INTO @TableA Values(14, 5)
INSERT INTO @TableA Values(25, 1)
INSERT INTO @TableA Values(16, 2)
INSERT INTO @TableA Values(17, 3)
INSERT INTO @TableA Values(18, 4)
INSERT INTO @TableA Values(19, 5)
INSERT INTO @TableA Values(100, 6)

INSERT INTO @TableB Values(100, 1)
INSERT INTO @TableB Values(19, 2)
INSERT INTO @TableB Values(12, 3)
INSERT INTO @TableB Values(13, 4)
INSERT INTO @TableB Values(14, 5)
INSERT INTO @TableB Values(27, 1)
INSERT INTO @TableB Values(17, 2)
INSERT INTO @TableB Values(17, 6)

SELECT a.col1, a.col2, b.col2 FROM @TableA a
INNER JOIN @TableB b on a.Col1 = b.Col1

--***************** This doesn't because of the GO statement.

declare @TableA TABLE(Col1 int, Col2 int)
declare @TableB Table(Col1 int, Col2 int)

INSERT INTO @TableA Values(1, 1)
INSERT INTO @TableA Values(111, 2)
INSERT INTO @TableA Values(12, 3)
INSERT INTO @TableA Values(13, 4)
INSERT INTO @TableA Values(14, 5)
INSERT INTO @TableA Values(25, 1)
INSERT INTO @TableA Values(16, 2)
INSERT INTO @TableA Values(17, 3)
INSERT INTO @TableA Values(18, 4)
INSERT INTO @TableA Values(19, 5)
INSERT INTO @TableA Values(100, 6)

INSERT INTO @TableB Values(100, 1)
INSERT INTO @TableB Values(19, 2)
INSERT INTO @TableB Values(12, 3)
INSERT INTO @TableB Values(13, 4)
INSERT INTO @TableB Values(14, 5)
INSERT INTO @TableB Values(27, 1)
INSERT INTO @TableB Values(17, 2)
INSERT INTO @TableB Values(17, 6)
GO

SELECT a.col1, a.col2, b.col2 FROM @TableA a
INNER JOIN @TableB b on a.Col1 = b.Col1

Duane.

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2004-05-07 : 10:26:39

Thanks for the help.

The only thing I did was change how I referrence the table and it worked prefect. When I saw you doing this, I gave it a try and it worked!

SELECT P_WORKCENTER, P_DATE, P_LOT, P_PROJECT, P_GROSSQTY, PT_DESC, PT_QTY
FROM @PRODUCTION A INNER JOIN @PRODUCTION_TOTALS B ON A.P_NUM = B.P_NUM;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources