仓库储存进程中实践动态Sql语句,NET中运用存款和储蓄进程的主意及其优点

原稿宣布时间为:二零零六-09-26 —— 来源于本身的百度文章 [由搬家工具导入]

初稿揭橥时间为:二零一零-09-26 —— 来源于自身的百度小说 [由搬家工具导入]

  在近日的连串中,有用到动态执行sql语句,SQL为我们提供了三种动态执行SQL语句的指令,分别是EXEC和sp_executesql;通常,sp_executesql则更有着优势,它提供了输入输出接口,而EXEC没有。还有三个最大的益处正是接纳sp_executesql,能够重用执行安插,那就大大提供了举行品质,还能编写更安全的代码。EXEC在某个意况下会更灵活。除非你有令人信服的说辞使用EXEC,否侧尽量利用sp_executesql。

一.用到存款和储蓄进度的亮点

一.应用存储进程的优点

1.EXEC的使用

   作为服务器端的代码,存款和储蓄进程具有以下优点:
1) 存款和储蓄进程是先期编写翻译过的,是履行查询也许批处理的最快方法.
2)在服务器而不是桌面电脑上进行顺序能够小幅度地降落互连网流量.
3)存款和储蓄进度是模块化的,易于安排,代码也便于修改.要是前端的主次是经过调用存款和储蓄进度就可以升官具有

   作为劳动器端的代码,存款和储蓄进度具有以下优点:
1) 存款和储蓄进程是先行编写翻译过的,是推行查询或许批处理的最快方法.
2)在服务器而不是桌面电脑上实施顺序能够急剧地降低网络流量.
3)存款和储蓄过程是模块化的,易于安顿,代码也易于修改.假诺前端的次第是经过调用存储进度就能够升官具有

    
EXEC命令有二种用法,一种是推行一个储存进程,另一种是履行三个动态的批处理。以下所讲的都以第二种用法。

客户的应用程序
4)存款和储蓄进程是数据库安全性的多少个主要组成都部队分.假设全数的用户都以经过存款和储蓄进程来拜会数据,那么,就

客户的应用程序
4)存储进程是数据库安全性的多个生死攸关组成部分.借使全体的用户都以透过存款和储蓄进度来访问数据,那么,就

  下边先使用EXEC演示1个例证,代码1

能够禁止用户对表的直接待上访问,并决定全体对数据的访问.

能够禁止用户对表的直接待上访问,并控制全数对数据的访问.

图片 1图片 2

二.在.NET中选取存款和储蓄进程中3个简便示例:

二.在.NET中采取存款和储蓄进程中叁个差不多示例:

1 DECLARE @TableName VARCHAR(50),@Sql NVARCHAR (MAX),@OrderID INT;
2 SET @TableName = 'Orders';
3 SET @OrderID = 10251;
4 SET @sql = 
5      'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE OrderID = '+
6      CAST(@OrderID AS VARCHAR(10))+' ORDER BY ORDERID DESC'
7 EXEC(@sql);

   private SqlCommand GetLoadCommand()
        {
            if ( loadCommand == null )
            {
                //
                // Construct the command since we don’t have it
already
                //
                loadCommand = new SqlCommand(“GetCustomerByEmail”,new
SqlConnection

   private SqlCommand GetLoadCommand()
        {
            if ( loadCommand == null )
            {
                //
                // Construct the command since we don’t have it
already
                //
                loadCommand = new SqlCommand(“GetCustomerByEmail”,new
SqlConnection

View Code

(DuwamishConfiguration.ConnectionString));
                loadCommand.CommandType = CommandType.StoredProcedure;
       
                loadCommand.Parameters.Add(new SqlParameter(EMAIL_PARM,
SqlDbType.NVarChar,

(DuwamishConfiguration.ConnectionString));
                loadCommand.CommandType = CommandType.StoredProcedure;
       
                loadCommand.Parameters.Add(new SqlParameter(EMAIL_PARM,
SqlDbType.NVarChar,

注:那里的EXEC括号中只同意包涵多个字符串变量,然而足以串联两个变量,倘使大家那样写EXEC:

50));
            }
           
            return loadCommand;
        }

50));
            }
           
            return loadCommand;
        }

  

  
在发轫化SqlCommand的参数中”GetCustomerByEmail”正是储存进度的称谓,而SqlCommand其余三个参数

  
在开端化SqlCommand的参数中”GetCustomerByEmail”正是储存进度的称号,而SqlCommand此外二个参数

EXEC(‘SELECT TOP(‘+ CAST(@TopCount AS VARCHAR(10)) +’)* FROM ‘+

是sqlconnection参数,用于链接数据库.而在”GetCustomerByEmail”的参数能够一贯写成sql的查询语句,

是sqlconnection参数,用于链接数据库.而在”GetCustomerByEmail”的参数能够直接写成sql的查询语句,

           QUOTENAME(@TableName) +’ ORDER BY ORDERID DESC’); 

在此间使用存款和储蓄进度不仅是读取和修改数据库的进度更便于管理,而且实施进度会加速
   在数据库”GetCustomerByEmail”的积存进度为:
   ————————————————–

在这边使用存款和储蓄进度不仅是读取和修改数据库的经过更易于管理,而且执行过程会加速
   在数据库”GetCustomerByEmail”的积存进度为:
   ————————————————–

      SQL编译器就会报错,编译不通过,而一旦大家这么:

— GetCustomerByEmail

CREATE PROCEDURE GetCustomerByEmail
    @Email NVARCHAR(50) = NULL
AS
    SET NOCOUNT ON

    SELECT c.PKId,
           c.Email,
           c.Password,
           c.Name,
           a.Address,
           a.Country,
           a.PhoneNumber,
           a.Fax
      FROM Customers c,
           Addresses a
     WHERE c.Email = @Email
       AND a.CustomerId = c.PKId

— GetCustomerByEmail

CREATE PROCEDURE GetCustomerByEmail
    @Email NVARCHAR(50) = NULL
AS
    SET NOCOUNT ON

    SELECT c.PKId,
           c.Email,
           c.Password,
           c.Name,
           a.Address,
           a.Country,
           a.PhoneNumber,
           a.Fax
      FROM Customers c,
           Addresses a
     WHERE c.Email = @Email
       AND a.CustomerId = c.PKId

  

    RETURN 0

— unique index on Customers (Email)

    RETURN 0

— unique index on Customers (Email)

EXEC(@sql+@sql2+@sql3);

— duplicate index on Addresses (CustomerId)

GO

三.
存储进程基本语法:(要详细精晓存款和储蓄进程里的语法,推荐看”使用Transact-SQL编制程序”)

1.向存款和储蓄进程传递数据
   1)能够在create procedure命令中的存款和储蓄进度名自此列出要拉长的参数.各类参数都必须以@打头.它

会成为该存款和储蓄进程中的局地变量
   2)在调用存款和储蓄进度中,必须为这么些输入变量提供值
例子:
create procedure CategoryGet( @CategoryName NVARCHAR(35))
AS
select ProductCategoryNameProductCategoryDesription
From dbo.ProductCategory
Where ProductCategorName=@CategoryName

EXEC CategoryGet ‘Kite’

  1. 参数的暗许值
    CREATE PROCEDURE StroredProcedure(
    @Variable DateType =DefaultVaule
    )

3.从存款和储蓄进程重返数据
1)
输出参数:无论在创立存款和储蓄进程的时候,照旧在调用它的时候,都不可能不使用首要字output.在蕴藏进程中,

出口参数就像局地变量一样.在调用存款和储蓄进度的程序依旧批处理中,必须在调用存款和储蓄进度此前创造接收输

出参数数据的变量.当存款和储蓄进程执行完成后,就会将出口参数的当前值传递给调用进度的有的变量.

例子:
Create proc GetProductName(
   @ProductCode CHAR(10),
   @ProductName VARCHAR(25) OUTPUT)
AS
Select @ProductName = ProductName
From dbo.Product
WHERE Code = @ProductCode

调用该存款和储蓄进度的批处理如下:
Declare @ProdName VARCHAR(25)
EXEC GetProductName ‘1001’, @ProdName OURPUT
Print @ProdName

  1. 使用RETURN命令
      
    return命令会无条件地平息存款和储蓄进度地履行,并向调用它地批处理依然客户重回一个值.
      
    重返值0表示执行成功,它也是暗中认可的回来值.-99到-1里面包车型客车值是Microsoft保留给SQLServer使用的,所

以指出使用-100要么更小的值来回到战败的状态.
  
在调用存款和储蓄进程时,必须依据如下格局在exec命令中央银行使二个有个其余整数型变量来取得重返的景况值.
DECLARE @IntLocalVariable INT
EXEC @IntLocalVariable= StoredProcedureName

  1. 回去数据的路径及其适用范围
       任意存储进度在选用再次回到数据的法虎时都面临着4种也许(select,
    raiserror,输出参数和return)
       适用范围如下:
       1)return和出口参数(output
    parameters)都以将数据再次回到给SQLserver中一贯调用存款和储蓄进程的次第或

者批处理
  
2)raiserror和select语句重回的记录集都以平昔回到给最后用户的客户应用程序的

四. 调试存储进度:

1)查看结果:
   
打开查询分析器,找到供给调剂的贮存进程,并按右键–打开,即会冒出两个”执行进程”的窗口,只要把

拥有参数值都赋好值,就能够见见该存款和储蓄进度的进行结果
2)单步跟踪:
   
同样打开查询分析器,找到必要调剂的存款和储蓄进程,并按右键–调节和测试.调节和测试器所要完毕的整套办事便是一

次一条命令地推行存款和储蓄进度.使用工具条上的按钮能够对单步执行代码的历程进展控制.在单步执行进度的

时候,使用”单步进入”(Step into)能够 执行单条命令.使用”单步跳过”(Step
over)按钮也足以执行单条

命令,假诺当前的下令调用了另叁个存储进度,它将会把那几个蕴藏进程作为一个完好无缺的单元来实施,不会跟

踪进入.使用”运维至光标”(Step to
cursor)按钮能够连接实施多条命令,知清宣宗标所钦赐的那一行命令为

止.

注:要给以登陆用户系统一管理理员的权柄,才方可进行对存款和储蓄进度的调节和测试.

— duplicate index on Addresses (CustomerId)

GO

三.
存款和储蓄进程基本语法:(要详细询问存款和储蓄进程里的语法,推荐看”使用Transact-SQL编制程序”)

1.向存储进程传递数据
   1)能够在create procedure命令中的存款和储蓄进程名自此列出要抬高的参数.每个参数都不可能不以@打头.它

会成为该存款和储蓄进程中的局地变量
   2)在调用存款和储蓄进程中,必须为那一个输入变量提供值
例子:
create procedure CategoryGet( @CategoryName NVARCHAR(35))
AS
select ProductCategoryNameProductCategoryDesription
From dbo.ProductCategory
Where ProductCategorName=@CategoryName

EXEC CategoryGet ‘Kite’

  1. 参数的暗许值
    CREATE PROCEDURE StroredProcedure(
    @Variable DateType =DefaultVaule
    )

3.从存款和储蓄进程再次回到数据
1)
输出参数:无论在开立存款和储蓄进度的时候,照旧在调用它的时候,都不可能不使用首要字output.在存款和储蓄进度中,

输出参数就像局部变量一样.在调用存款和储蓄进程的程序还是批处理中,必须在调用存款和储蓄进度此前创设接收输

出参数数据的变量.当存款和储蓄进度执行完毕后,就会将出口参数的当前值传递给调用进程的一对变量.

例子:
Create proc GetProductName(
   @ProductCode CHAR(10),
   @ProductName VARCHAR(25) OUTPUT)
AS
Select @ProductName = ProductName
From dbo.Product
WHERE Code = @ProductCode

调用该存款和储蓄进程的批处理如下:
Declare @ProdName VARCHAR(25)
EXEC GetProductName ‘1001’, @ProdName OURPUT
Print @ProdName

  1. 使用RETURN命令
      
    return命令会无条件地甘休存款和储蓄进程地履行,并向调用它地批处理大概客户再次回到叁个值.
      
    再次来到值0表示执行成功,它也是暗中同意的归来值.-99到-1里边的值是Microsoft保留给SQLServer使用的,所

以建议选用-100依然更小的值来回到失败的状态.
  
在调用存款和储蓄进度时,必须依据如下情势在exec命令中央银行使一个某些的整数型变量来赢得重返的动静值.
DECLARE @IntLocalVariable INT
EXEC @IntLocalVariable= StoredProcedureName

  1. 回到数据的途径及其适用范围
       任意存款和储蓄进度在采取再次回到数据的法门时都面临着4种大概(select,
    raiserror,输出参数和return)
       适用范围如下:
       1)return和输出参数(output
    parameters)都以将数据重回给SQLserver中平昔调用存款和储蓄进度的次序或

者批处理
  
2)raiserror和select语句重返的记录集都是直接回到给最后用户的客户应用程序的

四. 调节和测试存款和储蓄进度:

1)查看结果:
   
打开查询分析器,找到须要调剂的贮存进程,并按右键–打开,即会冒出八个”执行进度”的窗口,只要把

抱有参数值都赋好值,就足以见见该存款和储蓄进度的进行结果
2)单步跟踪:
   
同样打开查询分析器,找到须要调剂的存款和储蓄进度,并按右键–调节和测试.调节和测试器所要实现的漫天做事正是一

次一条命令地实施存款和储蓄进度.使用工具条上的按钮可以对单步执行代码的进度实行控制.在单步执行进程的

时候,使用”单步进入”(Step into)能够 执行单条命令.使用”单步跳过”(Step
over)按钮也足以实施单条

命令,假如当前的下令调用了另贰个储存进程,它将会把那些蕴藏进度作为3个完好无缺的单元来实施,不会跟

踪进入.使用”运维至光标”(Step to
cursor)按钮能够连接实施多条命令,知道光帝标所内定的那一行命令为

止.

注:要赋予登陆用户系统一管理理员的权能,才能够进行对存款和储蓄进度的调节和测试.

编译器就会由此;
 
     
所以最佳的做法是把代码构造到三个变量中,然后再把该变量作为EXEC命令的输入参数,那样就不会受限制了。
 
     
EXEC的老毛病是不提供接口,那里的接口是指,它不能够实施八个含有贰个带变量符的批处理,如下:

图片 3图片 4

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

View Code

主要就在SET
@sql这一句话中,借使大家运营那几个批处理,编写翻译器就会生出一下张冠李戴

  Msg 137, Level 15, State 2, Line 1 
  必须证明标量变量 “@OrderID”。

     
使用EXEC时,假诺你想拜会变量,必须把变量内容串联到动态构建的代码字符串中,如:

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

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

      串联变量的情节也设有品质方面包车型大巴坏处。SQL
Server为每多个的查询字符串创建新的履行布署,就算查询格局相同也是那般。为示范那一点,先清空缓存中的执行安插

  DBCC FREEPROCCACHE (这一个不是本文所涉嫌的内容,您可以查看MS的MSDN)

      将代码1运行一遍,分别对@OrderID
赋予下边三个值,10251,10252,10253。然后使用上边包车型客车代码查询

SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE
sql NOT LIKE ‘%cach%’ AND sql NOT LIKE ‘%sys.%’ 

     
点击F5周转,大家得以看看,每执行二遍都要爆发3遍的编写翻译,执行安排没有赢得丰裕重用。

     
EXEC除了不帮忙动态批处理中的输入参数外,他也不协理出口参数。默许景况下,EXEC把询问的输出重临给调用者。例如下边代码再次回到Orders表中具有的记录数

图片 5图片 6

1 DECLARE @sql NVARCHAR(MAX)
2 SET @sql = 'SELECT COUNT(ORDERID) FROM Orders';
3 EXEC(@sql);

View Code

 但是,如若您要把出口再次来到给调用批处理中的变量,事情就没有那么简单了。为此,你无法不接纳INSE帕杰罗T
EXEC语法把出口插入到二个对象表中,然后从那表中拿走值后赋给该变量,就像这么:

图片 7图片 8

1 DECLARE @sql NVARCHAR(MAX),@RecordCount INT
2 SET @sql = 'SELECT COUNT(ORDERID) FROM Orders';
3 CREATE TABLE #T(TID INT);
4 INSERT INTO #T EXEC(@sql);
5 SET @RecordCount = (SELECT TID FROM #T)
6 SELECT @RecordCount
7 DROP TABLE #T2

View Code

2.sp_executesql的使用

sp_executesql命令在SQL
Server中引入的比EXEC命令晚一些,它最首要为重用执行安排提供更好的支撑。

为了和EXEC作贰个眼看的对照,大家看看如若用代码1的代码,把EXEC换来sp_executesql,看看是否拿走大家所企盼的结果

图片 9图片 10

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

View Code

留意最终一行;事实注明能够运作;

sp_executesql提供接口

   
sp_executesql命令比EXEC命令更灵敏,因为它提供2个接口,该接口及帮忙输入参数也补助出口参数。那意义使您能够创制带参数的查询字符串,这样就能够比EXEC更好的选定执行安顿,sp_executesql的结合与仓库储存进程出色相似,分化之处在于您是动态构建代码。它的构成包蕴:代码快,参数注脚部分,参数赋值部分。说了那般多,照旧看看它的语法:

图片 11

EXEC sp_executesql

@stmt= <statement>,–类似存款和储蓄进程主体

@params = <params>, –类似存款和储蓄进程参数部分,注脚参数类型

<params assignment> –类似存款和储蓄进度调用,为参数赋值,参数值要和参数顺序要挨个对应,也能够由此为参数指明参数值的点子为其赋值

@stmt参数是输入的动态批处理,它能够引入输入参数或输出参数,和仓库储存进程的重头戏语句一样,只但是它是动态的,而存款和储蓄进度是静态的,不过你也能够在储存进程中使用sp_executesql;

 @params参数与概念输入/输出参数的积存进程头类似,实际上和储存进程头的语法完全一样;

 @<params assignment> 与调用存款和储蓄进度的EXEC部分类似。

     其实@stmt,@params能够总结,那么exec
sp_executesql的语法就足以简写成如下格式:

图片 12

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

图片 13

     

   
 为了验证sp_executesql对履行布置的管制优于EXEC,作者将采取前面研商EXEC时用到的代码。

图片 14

图片 15代码

DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID INT; 
SET @TableName = ‘Orders ‘;
SET @OrderID = 10251; 
SET @sql = ‘SELECT * FROM ‘+@TableName + ‘ WHERE OrderID = @OID ORDER
BY ORDERID DESC’ 
–注意当要对动态sql语句的表名进行参数化时,不得以如下表示:
–set @sql=’select * from @TableName where OrderID=@OID ORDER BY
Orderid desc’,
–尽管那样会提示必须证明标量变量@TableName,只可以够如下面所写的同等,将表名@TableName作为变量名举行拼接

EXEC sp_executesql 
@sql, 
N’@OID int ‘, 
@OID = @OrderID

图片 16

     上边大家看看exec
sp_executesql的执行功效,在调用该代码和反省它生成的施行安顿前,先清空缓存中的执行布置;

  DBCC FREEPROCCACHE

     将地方的动态代码执行1回,每趟执行都赋予@OrderID
区别的值,然后查询sys.syscacheobjects表,并留意它的输出,优化器只创建了1个备用陈设,而且该陈设被引用的一回

SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects

  WHERE sql NOT LIKE ‘%cache%’ AND sql NOT LIKE ‘%sys.%’ AND sql NOT
LIKE ‘%sp_executesql%’

     点击F5运行。

    
sq_executesql的另3个与其接口有关的有力功能是,你能够行使输出参数为调用批处理中的变量重临值。利用该成效能够免止用一时表再次回到数据,从而获得更神速的代码和更少的重新编写翻译。定义和使用输出参数的语法与仓库储存进度看似。也正是说,你要求在宣称参数时钦赐OUTPUT子句。例如,上边包车型客车静态代码简单的示范了何等从动态批处理中运用输出参数@p把值再次来到到表面批处理中的变量@i.

图片 17

DECLARE @sql AS NVARCHAR(12),@i AS INT;
SET @sql = N’ SET @p = 10′;
EXEC sp_executesql
@sql, 
N’@p AS INT OUTPUT’,
@p = @i OUTPUT
SELECT @i –该代码再次回到输出10

图片 18

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

      总括以下几点:   一.使用exce
sp_executesql效用比exec要高,同一类型的言辞,只需编写翻译三遍即可,而exec执行四次就须要编写翻译四次。
  二.构造动态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’

  三.无论是Exec还是Exec
sp_executesql,假设想要将表名和列名进行动态参数化,不得以选择表名参数和列名参数来开始展览站位,而且表名参数和列名参数要求选用存款和储蓄进度的参数.对    
于exec sp_executesql来说,不得以将表名参数和列名参数在钦命为在exec
sp_executesql参数证明部分注明的参数,如:

图片 19

图片 20代码

create PROCEDURE GetData
@tbName nvarchar(10),
@colName nvarchar(10),
@Name nvarchar(10)
AS
BEGIN
declare @sql nvarchar(50);
set @sql=’select ‘+ @colName+’ from ‘ +@tbName+ ‘ where
name=@whereName’;
–注意此句不得以写成如下:
— set @sql=’select @colName from @tbName where name=@whereName’;
exec sp_executesql 
@sql,
N’@whereName nvarchar(10)’,
@Name
END

图片 21