动态组合sql语句详解

在数据库开发过程中，我们经常会需要在存储过程中组合动态sql语句，以满足开发需要，动态sql语句主要是用到一个系统存储过程： sql_executesql来执行组合的sql语句，如果你对sp_executesql还不了解，请忽略下文，先到网上查清楚 sp_executesql是干嘛用的：），可以访问微软的官方解决：http://msdn2.microsoft.com/zh- cn/library/ms188001.aspx]]>

在数据库开发过程中，我们经常会需要在存储过程中组合动态sql语句，以满足开发需要，动态sql语句主要是用到一个系统存储过程：sql_executesql来执行组合的sql语句，如果你对sp_executesql还不了解，请忽略下文，先到网上查清楚sp_executesql是干嘛用的：），可以访问微软的官方解决： http://msdn2.microsoft.com/zh-cn/library/ms188001.aspx

举个简单例子，我们需要一个存储过程对数据根据条件进行汇总，表结构如下：

 CREATE TABLE tmp_Table(  
  ID INT,   --ID编号  
  t_Total INT,  --总量  
  t_Name VARCHAR(100), --名称  
  t_Type INT  --类型  
 )

现在我们要满足以下条件：
1、要求通过存储过程OUTPUT返回“总量、类型、名称”
2、当指定t_Type的值为-1时，我们查询第一条记录；反之则查询指定t_Type的第一条记录

根据条件我们写出存储过程基本结构如下：

 CREATE PROCEDURE p_TestExecuteSql  
   @t_Type INT,    --指定t_type的值  
   @ReturnTotal INT,   --返回总量  
   @ReturnName VARCHAR(100) OUTPUT, --返回名称  
   @ReturnType INT OUTPUT   --返回类型  
  BEGIN  
   --执行相应的sql语句  
   DECLARE @sql NVARCHAR(1000)    --注意这里一定要是NVARCHAR的类型  
   SET @sql = N'SELECT TOP 1 @Total = t_Total, @Name = t_Name, @Type = t_Type FROM tmp_Table'  
   IF t_Type <> -1 SET @sql = @sql + N' WHERE p_Type = ' + CONVERT(NVARCHAR, @t_Type)  
 
   EXEC  
    sp_executesql @sql,  
    N'@Total INT OUTPUT, @Name VARCHAR(100) OUTPUT, @Type INT OUTPUT',  
    @ReturnTotal OUTPUT,  
    @ReturnName OUTPUT,  
    @ReturnType output  
  END

我们来分析p_TestExecuteSql这个存储过程几个重点：
1.

 DECLARE @sql NVARCHAR(1000)

定义一个sql语句的变量，长度根据你估计组合的sql语句会有多长，需要注意的是一定要用NVARCHAR类型，关于NVARCHAR类型，请到网上查询相关资料
2.

 EXEC  
    sp_executesql @sql,  
    '@Total INT OUTPUT, @Name VARCHAR(100) OUTPUT, @Type INT OUTPUT',  
    @ReturnTotal OUTPUT,  
    @ReturnName OUTPUT,  
    @ReturnType output

你可能不明白@Total，@Name，@Type这些变量并没有申明却为何可以使用，那么你需要了解sp_executesql的工作方式，sp_executesql实质就是把你组合的sql语句重组成一个存储过程再执行。了解这个本质，你就不难明白为什么要这么写了，例如本例，我们调用p_TestExecuteSql这个存储过程如下：

 DECLARE @ReturnTotal INT,@ReturnName VARCHAR(100), @ReturnType INT  
 exec p_TestExecuteSql 0, @ReturnTotal OUTPUT, @ReturnName OUTPUT, @ReturnType OUTPUT

存储过程p_TestExecuteSql组合sql语句，组合的sql语句如下：

SELECT @Total = t_Total, @Name = t_Name, @Type = t_Type FROM tmp_Table WHERE p_Type = 0

当我们调用sp_executesql执行这条语句时，就相当于sp_executesql为我们创建了一个存储过程再执行，创建的存储过程就像这样：

 CREATE PROCUDRE p_TmpProc  
     @Total INT OUTPUT,  
     @Name VARCHAR(100) OUTPUT,  
     @Type INT OUTPUT  
 BEGIN  
     SELECT @Total = t_Total, @Name = t_Name, @Type = t_Type FROM tmp_Table WHERE p_Type = 0  
 END

　　

注意：本文为我的独立博客镜像博客，自发表不再更新，原文可能随时被更新，敬请访问原文。同时，请大家不要在此评论，如果有什么看法，请点击这里：http://iove.net/1705/

本文来自http://iove.net，欢迎转载，转载敬请保留相关链接，否则视为侵权，原文链接：http://iove.net/1705/

转载于:https://www.cnblogs.com/conis/archive/2007/07/16/1720602.html