Must declare the table variable

sql-server tsql data-import
Brainchild picture Brainchild · May 10, 2012 · Viewed 7.3k times · Source

I come across an error while writing some SQL statements for data imports.

As I am doing data port, I need to declare some temporary table variables.

I declared a table variable at the beginning of the file and performed some manipulations (while loops, insertions and updates) on the table variable. Later in the middle of the scripts in another while loop if I access this table variable the script parsing giving below error

Must declare the table variable @temptable

Appreciate your help.

Answer

Seann Alexander picture Seann Alexander · May 10, 2012

If a previously declared Variable is no longer available in a block of executed SQL Code, it is likely that a GO statement has been called.

As per MSDN, "The scope of a local variable is the batch in which it is declared.".

A Go Statement "Signals the end of a batch of Transact-SQL statements to the SQL Server utilities."

It is recommended to double check your SQL code for any errant GO statements.

References:

DECLARE @local_variable (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188927.aspx

GO (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188037.aspx

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
OR is not supported with CASE Statement in SQL Server

The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this? CASE ebv.db_no WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' ELSE 'WECS 9520' END as wecs_system

Read More