SQL Server存储过程中执行动态SQL语句

一、简介

在Sql Server中执行动态sql语句有两种方法：exec和exec sp_executesql。
优缺点：
1、exec sp_executesql提供输入、输出接口，可以在sql语句中进行参数占位；exec则不可以。
2、能够重用执行计划，提高了执行效率；exec则不可以。
3、exec sp_executesql更安全，可以防止注入；exec则不可以。
因此，编者在这里推荐使用exec sp_executesql。

二、exec的使用方法

exec有两种用途：一种用来执行存储过程；一种是在存储过程中执行sql语句。
例如：

declare @tablename varchar(20);
declare @sql nvarchar(200);
declare @id int;
declare @name varchar(20);
set @tablename = 'user';
set @id = 3;
set @name = 'ann';
set @sql = 'select * from ' + @tablename + 'where id = ' + convert(varchar(10),@id);
/*
set @sql = 'select * from ' + @tablename + 'where name = ' + @name;
注意：这样会出错。因为执行时sql为：select * from user where name =ann;
然而ann是一个字符串常量，需要加单引号，编译器才能执行。
此时应该用下面介绍的第二种方法。
*/
exec @sql;

下面给出一些容易犯的错误：

1、@sql变量的类型必须为nvachar。
2、声明的变量不能放在单引号里面，因为现在是在拼接字符串，exec不支持输入输出参数。

三、exec sp_executesql使用方法

exec sp_executesql语法结构包括：sql语句、参数部分。参数复制部分。如下所示：

<span style="font-family:SimSun;font-size:24px;">exec sp_executesql
   @stmt= <statement>,--sql语句
   @params = <params>, --声明参数类型
   <params assignment> --为参数赋值</span>

其中@stmt和@params可以省略。

举例说明：

<span style="font-family:SimSun;font-size:24px;">declare @tablename varchar(20);
declare @sql nvarchar(200);
declare @id int;
declare @name varchar(20);
set @tablename = 'user';
set @id = 3;
set @sql = 'select set @name = name from ' + @tablename + 'where id = @id';

exec sp_executesql
	@sql,
	N'@id int ,@name varchar(20) output',
	@id ,@name output</span>

其中@id是输入参数，@name是输出参数。执行过后，@name变量就有值了。

下面给出一些常见的错误：

1、为参数赋值时，一定要和参数声明时的顺序保持一致。当然也可以指定参数名进行赋值。

2、对于表名、列名这些变量不能写在单引号里面，一定要用加号进行连接。否则会出错，提示你要生命表变量等。

3、@sql变量类型一定要为nvarchar，否则出错。