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,否则出错。





已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页