宣告表變數
DECLARE @Employees TABLE
(
EmployeeID INT NOT NULL PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
ManagerID INT NULL
)
建立普通表時,使用 CREATE TABLE Name (Columns)
語法。建立表變數時,使用 DECLARE @Name TABLE (Columns)
語法。
要在 SELECT
語句中引用表變數,SQL Server 要求你為表變數賦予別名,否則你將收到錯誤:
必須宣告標量變數“@TableVariableName”。
即
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