存储过程中执行动态sql语句

存储过程中执行动态sql语句
MSSQL提供了两种方式：exec和sp_executesql
通常后者更有优势，提供了输入输出接口，而exec没有
sp_executesql的最大优点是能够重用执行计划，大大提高了执行性能，所以尽量使用sp_executesql，它也更灵活

1 exec的使用

exec有两种用法：执行一个存储过程，另一种是执行一个动态的批处理
EXEC括号中只允许包含一个字符串变量，但是可以串联多个变量，例如：
XEC('SELECT TOP('+ CAST(@TopCount AS VARCHAR(10)) +')* FROM '+
　　　　　　　　　　QUOTENAME(@TableName) +' ORDER BY ORDERID DESC');
这样编译器会报错，编译不通过
但可以这样：EXEC(@sql+@sql2+@sql3);编译就会通过

最好的办法就是把代码构造到一个变量中，然后再把该变量作为exec命名的输入参数

exec的缺点是不能执行一个包含一个带有变量的批处理

 例如：

  DECLARE @TableName VARCHAR(50),@Sql NVARCHAR(MAX),@OrderID INT;
  SET @TableName = 'Orders';
  SET @OrderID = 10251;
  SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +
 　　　　'WHERE OrderID = @OrderID ORDER BY ORDERID DESC'
  EXEC(@sql);

使用EXEC时，如果您想访问变量，必须把变量内容串联到动态构建的代码字符串中，如：

SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +

 　　'WHERE OrderID = '+CAST(@OrderID AS VARCHAR(10))+' ORDER BY ORDERID DESC'

Exec除了不支持动态批处理中的输入参数外，也不支持输出参数，默认情况下，exec把查询的输出返回给调用者，

然而：如果要把输出返回给调用批处理中的变量，就很麻烦，必须使用 insert exec语法把输出插入到一个目标表，然后从这表中获取值后赋值给该变量

 

 DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID INT ,@sql2 NVARCHAR(MAX);
 SET @TableName = 'Orders ';
 SET @OrderID = 10251;
 SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) + ' WHERE OrderID = '+CAST(@OrderID AS VARCHAR(50)) + ' ORDER BY ORDERID DESC'
 EXEC sp_executesql @sql

2 sp_executesql

sp_executesql 支持输入参数也支持输出参数，这个功能可以创建带参数的查询字符串
语法：代码块，参数声明部分，参数赋值部分

EXEC sp_executesql

@stmt= <statement>,--类似存储过程主体

@params = <params>, --类似存储过程参数部分,声明参数类型

<params assignment> --类似存储过程调用，为参数赋值,参数值要和参数顺序要一一对应，也可以通过为参数指明参数值的方式为其赋值
@stmt：是输入的动态批处理，它可以引入输入参数或者输出参数，和存储过程主体一样，只不过它是动态的，而存储过程是静态的，也可以在存储过程中使用sp_executesql
@params参数与定义输入/输出参数的存储过程头类似，实际上和存储过程头的语法完全一样；
@<params assignment> 与调用存储过程的EXEC部分类似。

其实@stmt,@params可以省略，那么exec sp_executesql的语法就可以简写成如下格式：

 

 EXEC sp_executesql
   <statement>,
   <params>,
   <params assignment>

sq_executesql的另外一个强大的功能是：可以使用输出参数为调用批处理的变量返回值，该功能可以避免用临时表返回数据，定义和使用输出参数的语法与存储过程类似，需要在声明时候指定 output 子句

 

以字母N为前缀标识字符串常量

总结：
1 使用exec sp_executesql效率要比exec要高，同一类型的语句，只需编译一次即可，而exec执行几次就编译几次
2 构造动态sql的where子句，也就是条件子句时，exec无法使用变量来进行占用， 需要将变量转换成字符串，然后和动态sql进行拼接，这可能引起sql注入问题
如：SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +
　　' WHERE OrderID = '+CAST(@OrderID AS VARCHAR(50)) + ' ORDER BY ORDERID DESC'

而exec sp_executesql：则可以使用变量来进行站位，以后再给这个参数传值的方式构造动态sql，这样就避免了sql注入问题

SET @sql = 'SELECT * FROM '+@TableName + ' WHERE OrderID = @OID ORDER BY ORDERID DESC'

3 无论是exec还是exec sp_executesql，如果想要将列名和表名动态参数化，不可以使用列名和表名来进行站位，