Scope of a variable table 2

[mhoyt]

I noticed in gmmastros reply to the surgery 35 day calander question that he used a variable defiend as a table. New one on me so I decided to see if I could create a variable table and join it to an existing table.

What I found was that while I could create the variable table and see what was in it, when I joined it to another table the new SELECT wants me to declare the variable again. Which makes me thing the variable has only a limited scope and that is frankly an area I need to learn more about.

So, how come my final SELECT does not know that the variable is in the following?

USE SANDBOX

IF(OBJECT_ID('SANDBOX..TEST') IS NOT NULL) DROP TABLE TEST
CREATE TABLE TEST (NBR INT, FOLKS VARCHAR(10))
INSERT INTO TEST VALUES (1, 'ABE')
INSERT INTO TEST VALUES (2, 'BUCK')
INSERT INTO TEST VALUES (3, 'CHUCK')
INSERT INTO TEST VALUES (4, 'DAVE')
GO
DECLARE @VARTABLE TABLE(EVENNUM INT)
DECLARE @NUM INT
SET @NUM = 2

WHILE @NUM <= 10
	BEGIN
		INSERT INTO @VARTABLE VALUES (@NUM)
		SET @NUM = @NUM + 2
	END

--Next will return five even numbers
SELECT EVENNUM FROM @VARTABLE

--Next errors, wants me to declare @VARTABLE
--even if the above SELECT is commented out.
SELECT FOLKS 
FROM TEST JOIN @VARTABLE ON TEST.NBR = @VARTABLE.EVENNUM

Also, can you give me some example sof when you might want to use a variable defined as a table?

TIA Mike

 

[gmmastros]

You need to use aliases when you do this kind of thing


SELECT FOLKS
FROM TEST
JOIN @VARTABLE [!]V[/!]
ON TEST.NBR = [!]V[/!].EVENNUM


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

TAble variables are used in place of temp tables where possible becasue they run purely in memory and thus are generally much faster.

However they have limitations that temp tables do not. For one, they only are in scope for the group of statments processed together. So if you are going to do multiple things and need to check your results before testing the next section of code, they will go out of scope. When I need to do this I udually start using a temp table so I can run each portion of the code separately until I have it completely worked out. Then I change over and use a table variable.

Another limitation is that you cannot use an execute statement as the source for inserting data into a table variable, but you can with a temp table.


Questions about posting. See faq183-874
Click here to help with Hurricane Relief