Microsoft SQL Server Variables Declare a Table Variable
Example
DECLARE @Employees TABLE
(
EmployeeID INT NOT NULL PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
ManagerID INT NULL
)
When you create a normal table, you use CREATE TABLE Name (Columns) syntax. When creating a table variable, you use DECLARE @Name TABLE (Columns) syntax.
To reference the table variable inside a SELECT statement, SQL Server requires that you give the table variable an alias, otherwise you'll get an error:
Must declare the scalar variable "@TableVariableName".
i.e.
DECLARE @Table1 TABLE (Example INT)
DECLARE @Table2 TABLE (Example INT)
/*
-- the following two commented out statements would generate an error:
SELECT *
FROM @Table1
INNER JOIN @Table2 ON @Table1.Example = @Table2.Example
SELECT *
FROM @Table1
WHERE @Table1.Example = 1
*/
-- but these work fine:
SELECT *
FROM @Table1 T1
INNER JOIN @Table2 T2 ON T1.Example = T2.Example
SELECT *
FROM @Table1 Table1
WHERE Table1.Example = 1
