存储过程中的动态语句执行举例

阿罗

用sp_executesql来执行动态生成的SQL语句，这个大家都知道，现在举一个常用的例子，以便大家参考起来方便一些：

看下面这个例子



在这个例子中，按照上面选取的条件，列出表格。注意到例如“重要性”这个下拉列表框，分为全部、高、中、低四个状态。如果重要性是“低”，那么选择语句将是select * from some-table where [重要性]='低'，如果选中的是“全部”，那么where [重要性]='低' 这个子句就不需要了。这么一来，对于选中的是“全部”或者高、中、低就需要两个不同的sql语句，而现在有三个下拉框，这就需要制造8条不同的sql语句来。如果用SQL服务器内置的sp_executesql过程，可以大大简化这个步骤，下面我就来详细说明上面这个例子的实现过程。

一个项目管理表格projects，结构大概如下：

pID 主键 int 自增

pName nvarchar(50) 项目名称

Description nvarchar(255) 项目描述

....

LastEdit datetime 最后修改日期

status int 项目进度的状态，fk,是另一个“进度”表的pk

importance tinyint 项目的重要程度，用数值大小表示

ownerID int 项目所有者，fk,是另一个“员工”表的pk

进度表的结构

id: pk

content nvarchar(50) 内容

ADP中有一个窗体，上面有两个日期textBox，可用来界定选择的日期区间，三个下拉列表框，分别可用来选择进度、重要度、所有者，例如"进度下拉列表框"的行来源是：

SELECT id,content FROM 进度表 UNION SELECT -1, '(全部状态)'

其余两个下拉列表框的构成方法也完全一样，都包含一个(全部)的选项，值为-1。

用一个按钮，根据这些选项值，来从数据库取出projects表记录，显示在一个子窗体中。

现在以项目所有者为例（暂不考虑其他），当下拉列表框选中（全部人员）时，SQL语句应该是

select * from projects

当选中某个人时

select * from projects where owner=选中的值

如果只有这一个,那么还好办,只要写两个SQL语句就好了,现在有三个下拉列表框,所以要写8组sql语句,那样的话,太麻烦了. 这时, sp_executesql就派上用场了.具体的用法是这样的

CREATE PROCEDURE sp_getProjectList

@startDate datetime,

@endDate datetime,

@status int,

@importance int,  ----这里不用与表定义一样的tinyint是为了VBA中传送int变量的方便,我们在后面作了一个显式转换

@owner int

AS

DECLARE @strSQL nvarchar(500)

SET NOCOUNT ON

SET @strSQL=

'SELECT * FROM projects t WHERE (t.lastEdit>@startDate2) AND (t.lastEdit<@endDate2) '

IF (@status<>-1)

SET @strSQL = @strSQL + ' AND (t.status=@status2) '

ELSE

SET @strSQL = @strSQL + ' AND (@status2=-1) '

IF @importance<>-1

SET @strSQL = @strSQL + ' AND (t.importance=CAST(@importance2 as tinyint)) '

ELSE

SET @strSQL = @strSQL + ' AND (@importance2=-1) '

IF @owner<>-1

SET @strSQL = @strSQL + ' AND (t.owner=@owner2) '

ELSE

SET @strSQL = @strSQL + ' AND (@owner2=-1) '

exec sp_executesql @strSQL, [url=http://www.office-cn.net/vvb/mailtN'@startDate2]N'@startDate2[/url] datetime,@endDate2 datetime,

         @status2 int, @importance2 int, @owner2 int',

         @startDate,@endDate,@status,@importance,@owner

GO

要注意的是参数的用法, 动态生成的sql语句中也可以使用参数,这个参数的名称同头上定义的是不一样的,我在这些参数后面都加了一个2, 最后一句exec sp_executesql @strSQL, N'.....内部参数列表....', 外部参数列表,这个用法,是非常方便的. 更详细的说明当然要看联机丛书,不过这个用法非常典型,我自己就好几次用到过.所以专门写一贴.

实际的情况,因为几个外键的存在,使整个select语句非常冗长,但是使用了sp_executesql,整个工作就显得清晰自然多了. 另外一个者的注意的地方是，按照联机丛书所说，如果直接用execute命令来执行sql语句，因为参数值不同，每个sql 字符串均是唯一的。尽管两种方法生成的批处理数相同，但因为 sp_executesql 生成的sql 字符串相似，所以查询优化程序更有可能反复使用执行计划。也就是说sp_executesql效率也是高的。

[此贴子已经被作者于2005-2-1 16:25:09编辑过]

西西弗斯

真妙，真的是不怕做不到，只怕想不到

cg1

其实和 ACCESS 的 JET SQL 组织语句非常相似，只是 VBA 中用 DOCMD.RUNSQL 或者 CURRENTPROJECT.CONNECTION.EXECUTE 或者 CURRENTDB.EXECUTE 来执行，而 SQL SERVER 中用 sp_executesql  这个系统存储过程来执行。