登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

梦想之鹰的天空

天高任鸟飞......放飞....心情..........放飞.....梦想

 
 
 

日志

 
 

在SQL Server中生成动态SQL语句  

2008-04-18 16:42:19|  分类: 数据库 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

在需要解决某个棘手的数据库问题时,生成SQL语句可作为一种强大的工具,虽然我们在使用它时必须十分小心。本文将探讨如何用这种功能来轻松地生成SQL语句。

  动态SQL语句

  一个动态的SQL语句是在执行时创建的,不同的条件生成不同的SQL语句。在我们需要决定运行时有哪些字段从SELECT语句返回时,在决定查询的不同标准时,动态地创建这些语句是很有用处的。

  这些SQL字符串不是为了语法分析以便于查找错误,因为它们是在运行时生成的,而且它们有可能将安全漏洞引入到你的数据库中。此外,SQL字符串有可能成为一个调试上恶梦,这就是为什么笔者并非动态生成SQL语句的一个痴迷者的原因。但在有些情况下,这种功能却是很不错的。

  一个动态的例子

  笔者经常回答的一个问题是“我如何将我的WHERE语句传递给一个存储过程?”,而且经常看到类似于下面的情况,其TSQL语法是非法的。

  DECLARE @WhereClause NVARCHAR(2000)

  SET @WhereClause = ' Prouct = ''Computer'''

  SELECT * FROM SalesHistory WHERE @WhereClause

  但情况并不如此简单,有时,需要额外的标准,而且随着数据表的逐渐增大,就需要越来越多的标准。这通常可以通过为不同的标准编写不同的存储过程而解决,不过有时每次执行的这种标准是如此迥然不同,以至于在一个存储过程中包含所有的可能性可能成为一个沉重的负担。虽然这些存储过程可以用于考虑每一个可能的WHERE语句,(当然这要依赖于不同的参数) ,这通常会引起性能上的降低,因为在WHERE子句中有太多的条件。

  让我们看看如何创建一个简单的动态查询。首先,我们需要一个表和一些查询的数据。下面的脚本创建了SalesHistory表并将数据装载到其中。

  CREATE TABLE [dbo].[SalesHistory]

  (

  [SaleID] [intIDENTITY(1,1),

  [Product] [varchar](10) NULL,

  [SaleDate] [datetime] NULL,

  [SalePrice] [money] NULL

  )

  GO

  SET NOCOUNT ON

  DECLARE @i INT

  SET @i = 1

  WHILE (@i <=5000)

  BEGIN

  INSERT INTO [SalesHistory](Product, SaleDate, SalePrice)

  VALUES ('Computer', DATEADD(ww, @i, '3/11/1919'),

  DATEPART(ms, GETDATE()) + (@i + 57))

  INSERT INTO [SalesHistory](Product, SaleDate, SalePrice)

  VALUES('BigScreen', DATEADD(ww, @i, '3/11/1927'),

  DATEPART(ms, GETDATE()) + (@i + 13))

  INSERT INTO [SalesHistory](Product, SaleDate, SalePrice)

  VALUES('PoolTable', DATEADD(ww, @i, '3/11/1908'),

  DATEPART(ms, GETDATE()) + (@i + 29))

  SET @i = @i + 1

  END

  下面我们创建一个可以接受WHERE子句的存储过程。为了达成这个例子的目的,笔者将假定WHERE子句是从调用客户应用程序中动态生成的。

  CREATE PROCEDURE usp_GetSalesHistory

  (

  @WhereClause NVARCHAR(2000) = NULL

  )

  AS

  BEGIN

  DECLARE @SelectStatement NVARCHAR(2000)

  DECLARE @FullStatement NVARCHAR(4000)

  SET @SelectStatement = 'SELECT TOP 5 * FROM SalesHistory '

  SET @FullStatement = @SelectStatement + ISNULL(@WhereClause,'')

  PRINT @FullStatement

  EXECUTE sp_executesql @FullStatement

  /*

  --也可用EXECUTE()执行相同的语句

  EXECUTE (@FullStatement)

  */

  END

  笔者在此设置@WhereClause允许NULL值,因为我们可能并不总是想为@WhereClause传递一个值。

  对这个存储过程的每次执行而言,每一个字段都从SalesHistory中返回前五行。如果为@WhereClause参数传递了一个值,执行语句将把此字符串添加到@SelectStatement字符串中。然后笔者使用了存储过程sp_executesql执行动态生成的SQL字符串。

  sp_executesql或 EXECUTE()

  在SQL Server中有两种方法执行动态SQL语句,一是使用sp_executesql系统存储过程,二是使用EXECUTE()。有时这两种方法可以产同样的结果,不过在其如何运行上却有着一些不同点。

  系统存储过程sp_executesql允许参数可被传递进入或传出动态的SQL语句,而EXECUTE()则不然。因为SQL语句是作为一个参数被传递给sp_executesql存储过程中的,与EXECUTE()相比,它不易受到SQL注入式攻击。因为sp_executesql是一个存储过程,所以将SQL字符串传递给它可以使SQL字符串有更多的机会被放置在高速缓存中。以笔者的观点,sp_executesql可以生成清晰而且容易阅读和维护的代码。这就是笔者为什么更喜欢用sp_executesql来执行动态SQL语句的原因。

  在笔者前面的例子中,我们看了如何通过将一个WHERE子句传递给一个存储过程而生成一个简单的SQL语句。不过,如果我们想从动态生成的SQL语句中得到参数值的列表该怎么办?笔者将使用sp_executesql,因为它准许我们输入和输出参数。

  我们要稍微修改一下最初的存储过程,这就可以将从SQL语句中返回的记录总数分配给一个输出参数。

  DROP PROCEDURE usp_GetSalesHistory

  GO

  CREATE PROCEDURE usp_GetSalesHistory

  (

  @WhereClause NVARCHAR(2000) = NULL,

  @TotalRowsReturned INT OUTPUT

  )

  AS

  BEGIN

  DECLARE @SelectStatement NVARCHAR(2000)

  DECLARE @FullStatement NVARCHAR(4000)

  DECLARE @ParameterList NVARCHAR(500)

  SET @ParameterList = '@TotalRowsReturned INT OUTPUT'

  SET @SelectStatement = 'SELECT @TotalRowsReturned = COUNT(*) FROM SalesHistory '

  SET @FullStatement = @SelectStatement + ISNULL(@WhereClause,'')

  PRINT @FullStatement

  EXECUTE sp_executesql @FullStatement, @ParameterList, @TotalRowsReturned = @TotalRowsReturned OUTPUT

  END

  GO

  在上面过程中,笔者需要声明一个参数列表,以传递给sp_executesql存储过程,因为在运行时将一个值分配给了变量。对sp_executesql调用的唯一一个变化是在usp_GetSalesHistory存储过程中,笔者将从调用中得到的输出参数分配给了本地的@TotalRowsReturned参数。

  我们还可以用与以前类似的方式调用usp_GetSalesHistory存储过程,不过在此增加了一个输出参数,用以指明返回的行。

  DECLARE @WhereClause NVARCHAR(2000), @TotalRowsReturned INT

  SET @WhereClause = 'WHERE Product = ''Computer'''

  EXECUTE usp_GetSalesHistory

  @WhereClause = @WhereClause,

  @TotalRowsReturned = @TotalRowsReturned OUTPUT

  SELECT @TotalRowsReturned

  小心为妙

  虽然笔者并不极力推荐动态SQL语句,但它确实是一个有用的工具。如果你决定要将动态SQL语句集成到实际的代码中,需谨慎对待。因为这种代码可以将一些潜在的漏洞引入到你的系统中;如果你认真对待了,这种代码可以灵活为你地解决一些问题。

  评论这张
 
阅读(504)| 评论(0)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018