sqlserver 脚本和批处理指令小结（2）

三.动态SQL：使用EXE命令生成代码
语法：EXEC/EXECUTE ({<字符串变量> | '<字面值命令字符串>'})
1.EXEC的作用域
真正的调用EXEC语句的行，拥有同该EXEC语句正在运行的批或过程中的其他代码相同的作用域。但是作为EXEC语句结果而被执行的代码，被认为是在它自
己的批中。
例如：
DECLARE @OutVar VARCHAR(50)
EXEC ('SELECT @OutVar = FirstName FROM Contact WHERE ContactID = 1')
这里系统会报错，指出必须声明变量@OutVar。因为EXEC的语句独自成为一个批处理，其中的变量不能和其外的作用域相沟通，只在这个批处理中有效。此
时，@OutVar的值为NULL。正确的写法如下：
EXEC ('DECLARE @OutVar VARCHAR(50)
SELECT @OutVar = FirstName FROM Contact WHERE ContactID = 1')
这里，我们看到两中不同的作用域，这两种作用域间不能相互沟通。如果不采用外部机制，比如一个临时表，我们就没有办法实现在内部作用域和外部作用域
之间传递信息。有一个例外的事情是可以在EXEC的区域内部出现，并且也能在EXEC执行后被看到，这就是系统函数。因此，像@@ROWCOUNT这样的变量仍然
能够被使用。
2.安全上下文和EXEC
当赋予某人权利运行一个存储过程，意味着他也能获得权利去执行存储过程内部的动作。比如，有一个存储过程用来列出去年内所有的雇佣员工。其中有权限
执行该存储过程的人，才能够执行并返回结果——即使他没有权限直接访问人力资源的员工表。
这样隐含权限对于EXEC语句是无效的。在默认情况下，任何在一个EXEC语句内部建立的参照，都将在当前用户的安全上下文中运行。因此，我们有权利去访
问一个叫spNewEmployee的存储过程，但是却没有权利去访问员工表。如果spNewEmployee通过一个简单的SELECT语句获得值，那么一切正常。但是如果
pNewEmployee使用EXEC语句去执行一个SELECT语句，这个EXEC语句将失败——因为没有权利访问员工表。
3.用户自定义函数和EXEC关联
不能在同一个语句中同时运行一个函数和EXEC语句。例如：
DECLARE @Num INT
SET @Num = 3
EXEC ('SELECT LEFT(LastName, ' + CAST(@Num AS VARCHAR) + ') AS FilingName FROM Contact')
这个语句会返回一个错误消息，因为CAST函数需要在EXEC所在行之前被解析。正确代码如下：
DECLARE @Num INT
DECLARE @str VARCHAR(255)
SET @Num = 3
SET @str = 'SELECT LEFT(LastName, ' + CAST(@Num AS VARCHAR) + ') AS FilingName FROM Contact'
EXEC (@str)
这个例子工作正常，因为EXEC的输入值已经是一个完整的字符串。
4.EXEC和用户自定义函数
一般来说，不允许用户自定义函数内部使用EXEC去运行动态SQL，但是，使用EXEC运行一个存储过程，少数情况是合法的。