Table variable error: Must declare the scalar variable "@temp"

sql-server tsql table-variable
objectWithoutClass picture objectWithoutClass · Aug 1, 2013 · Viewed 167.5k times · Source

I am trying to achieve:

declare @TEMP table (ID int, Name varchar(max))
insert into @temp SELECT ID, Name FROM Table

SELECT * FROM @TEMP 
WHERE @TEMP.ID  = 1        <--- ERROR AT @TEMP.ID

But I'm getting the following error:

Must declare the scalar variable "@temp".

What am I doing wrong?

Answer

Gordon Linoff picture Gordon Linoff · Aug 1, 2013

A table alias cannot start with a @. So, give @Temp another alias (or leave out the two-part naming altogether):

SELECT *
FROM @TEMP t
WHERE t.ID = 1;

Also, a single equals sign is traditionally used in SQL for a comparison.

Related questions

SELECT INTO a table variable in T-SQL

Got a complex SELECT query, from which I would like to insert all rows into a table variable, but T-SQL doesn't allow it. Along the same lines, you cannot use a table variable with SELECT INTO or INSERT EXEC queries. …

Read More
Creating an index on a table variable

Can you create an index on a table variable in SQL Server 2000? i.e. DECLARE @TEMPTABLE TABLE ( [ID] [int] NOT NULL PRIMARY KEY ,[Name] [nvarchar] (255) COLLATE DATABASE_DEFAULT NULL ) Can I create an index on Name?

Read More
Can I loop through a table variable in T-SQL?

Is there anyway to loop through a table variable in T-SQL? DECLARE @table1 TABLE ( col1 int ) INSERT into @table1 SELECT col1 FROM table2 I use cursors as well, but cursors seem less flexible than table variables. DECLARE cursor1 CURSOR FOR …

Read More