About me: My name is Maria Zakourdaev. I have more than 10 years experience with SQL Server (starting from SQL Server 6.5). The last five years have been spent mostly on benchmarking different SQL Server features and flows, like data import, indexes impact on DML flows, star transformations in RDBMS, Hierarchic queries and custom OLAP-like aggregations. I was a speaker in the Microsoft Teched (Israel) on the SQL Server track. I am also an active member of the Israel SQL Server Group.

Friday, April 3, 2009

Declaring table variable in SQL Server 2005

When there is a requirement to declare several local variables inside TSQL script, it appears that table variables must have it's own DECLARE clause. Surprisingly, Books Online do not mention that.

Here you go :

DECLARE @str1 varchar(8), @TblList table(TableName varchar(250))

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'table'.

DECLARE @TblList table(TableName varchar(250)), @str1 varchar(8)

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

DECLARE @str1 varchar(8)
DECLARE @TblList table(TableName varchar(250))

Command(s) completed successfully.

Labels: ,