8. SQLServer : 变量

NULLIF ( expression , expression )  

-- 创建 int 类型的变量
DECLARE @MyCounter INT;

-- 声明多个变量
DECLARE @LastName NVARCHAR(30), @FirstName NVARCHAR(20), @StateProvince NCHAR(2);

-- 引用变量
DECLARE @MyVariable INT;
SET @MyVariable = 1;
SELECT BusinessEntityID, NationalIDNumber, JobTitle
FROM HumanResources.Employee
WHERE BusinessEntityID = @MyVariable;

-- 赋值操作
DECLARE @EmpIDVariable INT;
SELECT @EmpIDVariable = MAX(EmployeeID)
FROM HumanResources.Employee;

-- 如果 SELECT 语句返回多行且变量引用非标量表达式，则该变量将设置为结果集中最后一行中表达式的返回值
DECLARE @EmpIDVariable INT;
SELECT @EmpIDVariable = BusinessEntityID
FROM HumanResources.Employee
ORDER BY BusinessEntityID DESC;

SELECT @EmpIDVariable;

-- 赋值操作
DECLARE @myvar CHAR(20);  
SET @myvar = 'This is a test';  
SELECT @myvar;  

-- 从查询中赋值
DECLARE @rows INT;  
SET @rows = (SELECT COUNT(*) FROM Sales.Customer);  
SELECT @rows;

-- 初始化赋值之后 使用select赋值
DECLARE @var1 VARCHAR(30);
SELECT @var1 = 'Generic Name';

SELECT @var1 = [Name]
FROM SalesLT.Product
WHERE ProductID = 1000000; --Value does not exist
SELECT @var1 AS 'ProductName';