Search code examples
sql-server-2008t-sqlsyntaxsql-server-2012declare

Why I can't declare a table variable together with others declarations?

For example:

DECLARE
    @t TABLE(id int),
    @i int;

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

But using separate DECLARE statements works fine

DECLARE @t TABLE(id int);

DECLARE
    @i int,
    @str varchar(10);

Command(s) completed successfully.

Why this happens? What is difference between table declaration and declaration of "usual" variable?

Solution

  • I don't know "why", exactly, but I have a couple of guesses:

    1. A table variable isn't as simple as an ordinary variable. It's not just a place in memory: behind the scenes, a temporary table is being created in tempdb. So perhaps the designers of the language wanted to distinguish between it and the more simple variables.

    2. Table variables have a more complex syntax than ordinary variable declarations. A declaration statement with multiple table variables plus multiple ordinary variables, plus variable initializations, could potentially get extremely complicated. Perhaps the syntax would just be too messy (or too ambiguous: open to multiple interpretations) for the parser.