NET中运用存款和储蓄进度的法子及其优点,存款和储蓄进度中履行动态Sql语句

原来的书文发布时间为:二零零六-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命令有三种用法,一种是执行八个囤积进程,另一种是实施1个动态的批处理。以下所讲的都以第三种用法。

客户的应用程序
4)存款和储蓄进程是数据库安全性的3个关键组成都部队分.借使拥有的用户都以经过存款和储蓄过程来走访数据,那么,就

客户的应用程序
4)存款和储蓄进程是数据库安全性的多个首要组成都部队分.若是持有的用户都是通过存款和储蓄进度来访问数据,那么,就

  上面先使用EXEC演示二个例证,代码1

能够禁止用户对表的直白访问,并操纵全数对数码的访问.

可以禁止用户对表的直白访问,并决定全体对数码的访问.

图片 1图片 2

二.在.NET中利用存款和储蓄进程中一个粗略示例:

二.在.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括号中只允许包蕴2个字符串变量,可是足以串联多少个变量,假设咱们这么写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命令中选用1个有些的整数型变量来赢得重临的动静值.
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)按钮也能够推行单条

指令,假如当前的一声令下调用了另二个仓库储存进度,它将会把这些蕴藏进程作为八个完好的单元来执行,不会跟

踪进入.使用”运行至光标”(Step to
cursor)按钮能够连接实施多条命令,知爱新觉罗·爱新觉罗·旻宁标所钦点的那一行命令为

止.

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

编写翻译器就会透过;
 
     
所以最佳的做法是把代码构造到3个变量中,然后再把该变量作为EXEC命令的输入参数,那样就不会受限制了。
 
     
EXEC的老毛病是不提供接口,那里的接口是指,它不能够履行三个蕴涵2个带变量符的批处理,如下:

图片 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运转2次,分别对@OrderID
赋予上边3个值,10251,10252,10253。然后使用上边的代码查询

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

     
点击F5周转,我们得以看到,每执行三次都要发出2回的编写翻译,执行陈设没有收获充裕重用。

     
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作3个鲜明的对照,我们看看假若用代码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命令更灵活,因为它提供三个接口,该接口及帮忙输入参数也支撑出口参数。那效用使你能够创设带参数的询问字符串,那样就足以比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

     将方面包车型客车动态代码执行一次,每趟执行都给以@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

 

相关文章