Sql Server系列：使用TransactSQL编程

1、变量

　　T-SQL变量是由declare命令声明的。声明时，需要declare命令的后面指定要声明的变量名及其数据类型。可以使用的数据类型包括create table命令中的所有数据类型，以及table和SQLvariant数据类型。在单个declare命令中声明多个变量时，需要使用逗号将它们相互隔开。

1.1、变量的默认值和作用域

　　变量的作用域（即可以使用变量的应用程序和变量的生命周期）只能是当前的批处理。新声明的变量默认值为空值，在表达式中使用它们之前必须为它们赋初值。

　　下面的脚步创建了两个测试变量，并展示了它们的初值和作用域。它由两个批处理构成的(由go相互隔开)，但它们却属于同一个执行过程。

DECLARE @Test INT,
        @TestTwo NVARCHAR(25)
SELECT @Test, @TestTwo
SET @Test = 1
SET @TestTwo = 'a value'
SELECT @Test, @TestTwo
GO
SELECT @Test, @TestTwo

　　执行结果如下：

　　第一个select返回了两个空值。在为这个两个变量赋值之后，第二个select正确地显示了这两个变量的新值。当这个批处理结束后(因为遇到了批处理的结束标志go)，所有变量的生命周期也就结束了。因此，最后一个select语句就返回了137号错误消息。

1.2、使用set和select命令

　　set和select命令都可以使用表达式为变量赋值。它们之间的主要区别在于：select可以从表、子查询或者视图中检索数据，并且也可以包含其他的select字句；而set命令则只能从表达式中获取数据。在set和select命令中都可以使用函数。

　　select语句可以检索多列，而每个列中的数据都可以赋值给一个变量。如果select语句返回了多个行，将会把结果集中最后一行的数据赋值给变量，系统不会报告任何的错误。

DECLARE @TempID INT,
        @TempName NVARCHAR(25)
SET @TempID = 100
SELECT @TempID = PersonID,
       @TempName = PersionName
FROM Persion

SELECT @TempID, @TempName

　　如果select语句没有返回任何行，那么它就不会改变变量的值。

1.3、条件select

　　因为select语句可以包含where字句，所以可以使用下面的语法为变量赋值：

SELECT @Variable = expression WHERE BooleanExpression

　　where字句的作用就像条件if语句一样。如果布尔表达式为真，就会执行select。如果为假，就不会执行select；因为没有执行select语句，当然就不会改变@Variable的值。

1.4、在SQL查询中使用变量

DECLARE @ProductNo CHAR(10)
SET @ProductNo = '10000'
SELECT ProductName FROM Product WHERE ProductNo = @ProductNo

2、流程控制

2.1、IF

 　　一个IF只能控制一条命令。

IF Condition
    Statement

2.2、Begin/End

　　一个if命令只能控制一条语句的执行与否，缺乏实用性。为解决这个问题，可以使用begin/end块，它可将多条命令作为一个整体构成if命令的下一条命令。

IF Condition
    Begin
        Multiple lines
    End

1>、IF Exists()

　　if exists()结构根据SQL select命令返回的结果集是否包含有进行来行判断。因为它只需要查看结果集中有没有行，所以在为它编写的select语句时应当检索全部的列(select *)。与检查@@rowcount>0的条件相比，这种方法的速度更快，因为它在判断时并不需要知道结果集中行的总数，只需要判断结果集中是否有行就可以。在if exists()中只要查询返回了一条记录，就可以停止执行查询，转而执行批处理中的其他语句。

IF EXISTS(SELECT * FROM [ORDER] WHERE [ORDERID]=100)
    BEGIN
        PRINT 'Process Orders'
    END

　　与在exists()函数中只是要select选择主键的方式相比，使用select * 的方法更好哦，这是由于两个原因造成的，首先，使用*的速度更快，因为此时SQL Server可以选择最快的索引来执行查询。其次，根据SQL Server的不同Service Pack级别，对于使用GUID作为主键的表来说，使用select选择主键的方法可能会失败。

2>、IF/ELSE

　　else命令是可选的，只有当if条件的计算结果为假时，才会执行放在else命令后面的语句。

3、临时表和表变量

3.1、局部临时表

　　创建临时表的方法与创建用户定义表的方式基本相同，只是在创建临时表时必须使用有#号开头的表名。临时表的生命周期很短暂。当创建它的批处理或过程结束时，临时表就被删除了。

CREATE TABLE #ProductTemp(
    ProductID INT PRIMARY KEY       
)

3.2、全局临时表

　　所有用户均可以引用全局临时表，只有当最后一个引用它的会话结束之后才会将它删除。创建全局临时表，需要使用由两个##开头的表名(##TableName)。

3.3、表变量

　　表变量类似于临时表，其优点在于它只存在内存之中。表变量与变量具有同样的作用域和生命周期，只有创建它们的批处理、过程或者函数才能够看到它们。当这些批处理、过程或者函数结束的时候，相应的表变量也就不再存在了。

DECLARE @WorkTable TABLE (
    ID INT PRIMARY KEY,
    Name VARCHAR(50)      
)
INSERT INTO @WorkTable(ID,Name) VALUES (1,'1000')
SELECT ID,Name FROM @WorkTable

3.4、动态SQL

　　动态SQL特别适合于完成以下工作：

　　◊ 利用多个查询条件来创建定制的where字句

　　◊ 根据where字句的内容，创建定制的from字句，使其只包含所需要的表和连接

　　◊ 根据用户要求，动态地创建不同的order by字句，按照不同的方式对数据进行排序

1>、执行动态SQL

EXEC[UTE] (T-SQL batch)
WITH RECOMPILE

　　使用with recompile选项可以强制SQL Server重新编译指定的T-SQL批处理，而不要使用以前执行过的查询执行计划。如果T-SQL字符串及参数变化较大，使用with recompile选项可以防止SQL Server使用与其不匹配的查询计划，从而造成性能的降低。但如果所执行的T-SQL字符串都是类似的语句，就不需要使用with recompile选项重新编译，这反而会降低性能。由于绝大多数动态SQL过程都会创建极为不同的SQL语句，所以在一般情况下使用with recompile选项都是合适的。

EXEC('SELECT * FROM Product WHERE ProductID=10')

2>、SP_EXECUTESQL

EXEC SP_EXECUTESQL
    'T-SQL' query
    Parameters Definition
    Parameter,Parameter,...

　　T-SQL的SQL语句和参数定义必须使用Unicode字符串。

　　使用参数可以优化性能。如果在每次只需T-SQL语句时都使用同样的参数，就应该使用SP_EXECUTESQL以及相应的参数来执行它，这样做可以保存查询计划，在今后执行该语句的时候就会优化性能。

EXEC SP_EXECUTESQL
    N'SELECT ProductName
       FROM Product
       WHERE ProductID=@ProductID',
    N'@ProductID INT',
    @ProductID=10