＃＃变量能否代替where子句查询？

[houjianxun]

如果where子句是一个变量，必须用exec来执行，类似这样：
exec("select field1,field2,field3 from mytable "+@where)

但部分条件需要当成参数传递，有什么好方法，就象我下面那段代码
select a.G_G_R_ID,a.G_G_R_Code,a.G_G_R_Name,a.G_G_R_Grave,a.G_G_R_Grave_Genre,a.G_G_R_A_Date from G_Grave_Reserve a,@indextable t where a.G_G_R_ID=t.nid and t.id>@PageLowerBound and t.id<=@PageUpperBound and @where order by a.G_G_R_ID desc;

[CrazyFor]

set @where=@where+' and aaa='+@aaa
.....

exec('select field1,field2,field3 from mytable '+@where)

[zjcxc]

不太明白,如果是传递的变量要带到where中,就好像蚂蚁的那样处理就行了.
delcare @where varchar(1000)
declare @para1 int,@para2 int
select @where=' where a.G_G_R_ID=t.nid '
select @where=@where+'and t.id>'+cast(para1 as varchar)
+' and t.id<='+cast(para2 as varchar)

exec('select field1,field2,field3 from mytable '+@where)

[houjianxun]

如果只是一个表我还会写，但另一个表是我事选声明的一个tabel变量
declare @indextable table(id int identity(1,1),nid int);

如果有一个tabel变量，该如何操作？
例如我下面写的
select a.G_G_R_ID,a.G_G_R_Code,a.G_G_R_Name,a.G_G_R_Grave,a.G_G_R_Grave_Genre,a.G_G_R_A_Date from G_Grave_Reserve a,@indextable t where a.G_G_R_ID=t.nid and t.id>@PageLowerBound and t.id<=@PageUpperBound and @where order by a.G_G_R_ID desc;

如何把它写成
exec('select a.G_G_R_ID,a.G_G_R_Code,a.G_G_R_Name,a.G_G_R_Grave,a.G_G_R_Grave_Genre,a.G_G_R_A_Date from G_Grave_Reserve a,@indextable t'+@where)

[net_steven]

似乎表名@indextable也是一个变量,那不如完全构建一个动态SQL:
declare csql varchar(4000)
csql='select a.G_G_R_ID,a.G_G_R_Code,a.G_G_R_Name,a.G_G_R_Grave,a.G_G_R_Grave_Genre,a.G_G_R_A_Date from G_Grave_Reserve a, '+@indextable+' t where a.G_G_R_ID=t.nid and t.id>'+cast(@PageLowerBound as int)+' and t.id<='+cast(@PageUpperBound as int) +' and '+@where+' order by a.G_G_R_ID desc '
exec(csql)

[happydreamer]

exec('select a.G_G_R_ID,a.G_G_R_Code,a.G_G_R_Name,a.G_G_R_Grave,
a.G_G_R_Grave_Genre,a.G_G_R_A_Date from G_Grave_Reserve a,'''+@indextable+''' t'+@where)

[houjianxun]

下面是我写的一个在asp.net中分页的存储过程
CREATE procedure AspNetPager_Grave_Reserve(@pagesize int,@pageindex int,@Serach varchar(1000))
as
set nocount on
begin
declare @indextable table(id int identity(1,1),nid int);
declare @PageLowerBound int;
declare @PageUpperBound int;
set @PageLowerBound=(@pageindex-1)*@pagesize;
set @PageUpperBound=@PageLowerBound+@pagesize;
set rowcount @PageUpperBound;
insert into @indextable(nid) select G_G_R_ID from G_Grave_Reserve where G_G_R_Flag=0 order by G_G_R_ID DESC;
select a.G_G_R_ID,a.G_G_R_Code,a.G_G_R_Name,a.G_G_R_Grave,a.G_G_R_Grave_Genre,a.G_G_R_A_Date from G_Grave_Reserve a,@indextable t where a.G_G_R_ID=t.nid and t.id>@PageLowerBound and t.id<=@PageUpperBound order by a.G_G_R_ID desc;
end
set nocount off
GO

上面是正常显示分页的一个存储过程，@Serach 是我想传入的搜索条件，@Serach有可能是G_G_R_ID=4或者是G_G_R_Code=220203250968
我想把它加到insert into @indextable(nid)....后面
或者加到select a.G_G_R_ID,a.G_G_R_Code....后面

[net_steven]

如果是表变量,那么declare以后的语句都必须包括在动态语句内:
declare csql varchar(8000)
csql='declare @indextable table(id int identity(1,1),nid int)'
csql=csql+'...'
csql=csql+'select a.G_G_R_ID,a.G_G_R_Code,a.G_G_R_Name,a.G_G_R_Grave,a.G_G_R_Grave_Genre,a.G_G_R_A_Date from G_Grave_Reserve a, @indextable t where a.G_G_R_ID=t.nid and t.id>'+cast(@PageLowerBound as int)+' and t.id<='+cast(@PageUpperBound as int) +' and '+@where+' order by a.G_G_R_ID desc '
exec(csql)

[houjianxun]

感谢楼上各位的回答

TO:net_steven(吃素的狼（瘦了）)
照你的方法做了，提示如下错误：
//-------------------------------
错误137:必须声明变量'@indextable'
//-------------------------------
我改后的如下：
set csql = 'select a.G_G_R_ID,a.G_G_R_Code,a.G_G_R_Name,a.G_G_R_Grave,a.G_G_R_Grave_Genre,a.G_G_R_A_Date from G_Grave_Reserve a,'+@indextable+' t where a.G_G_R_ID=t.nid and t.id>'+@PageLowerBound+' and t.id<='+@PageUpperBound+' order by a.G_G_R_ID desc';


To:happydreamer(小黑-从头学起)

照你的方法出现的错误和net_steven的一样，
照你的方法改后如下：
exec('select a.G_G_R_ID,a.G_G_R_Code,a.G_G_R_Name,a.G_G_R_Grave,a.G_G_R_Grave_Genre,a.G_G_R_A_Date from G_Grave_Reserve a,'''+@indextable+''' t where a.G_G_R_ID=t.nid and t.id>'+@PageLowerBound+' and t.id<='+@PageUpperBound+' order by a.G_G_R_ID desc');

[houjianxun]

To:net_steven
照你说的declare以后的语句都必须包括在动态语句内,检查语句是没有错误，但是程序出错了
改后如下：
set @csql= 'declare @indextable table(id int identity(1,1),nid int)';
set @csql = @csql +'insert into @indextable(nid) select G_G_R_ID from G_Grave_Reserve where G_G_R_Flag=0 order by G_G_R_ID DESC';
set @csql = @csql + 'select a.G_G_R_ID,a.G_G_R_Code,a.G_G_R_Name,a.G_G_R_Grave,a.G_G_R_Grave_Genre,a.G_G_R_A_Date from G_Grave_Reserve a,@indextable t where a.G_G_R_ID=t.nid and t.id>'+cast(@PageLowerBound as int)+' and t.id<='+cast(@PageUpperBound as int)+' order by a.G_G_R_ID desc';
exec(@csql);

出错内容如下：
//------------------------------
服务器: 消息 245，级别 16，状态 1，过程 AspNetPager_Grave_Reserve，行 16
将 varchar 值 'declare @indextable table(id int identity(1,1),nid int)insert into @indextable(nid) select G_G_R_ID from G_Grave_Reserve where G_G_R_Flag=0 order by G_G_R_ID DESCselect a.G_G_R_ID,a.G_G_R_Code,a.G_G_R_Name,a.G_G_R_Grave,a.G_G_R_Grave_Genre,a.G_G_R_A_Date from G_Grave_Reserve a,@indextable t where a.G_G_R_ID=t.nid and t.id>' 转换为数据类型为 int 的列时发生语法错误。
//------------------------------

[pengdali]

set @csql= 'declare @indextable table(id int identity(1,1),nid int) insert @indextable(nid) select G_G_R_ID from G_Grave_Reserve where G_G_R_Flag=0 order by G_G_R_ID DESC select a.G_G_R_ID,a.G_G_R_Code,a.G_G_R_Name,a.G_G_R_Grave,a.G_G_R_Grave_Genre,a.G_G_R_A_Date from G_Grave_Reserve a,@indextable t where a.G_G_R_ID=t.nid and t.id>'+cast(@PageLowerBound as varchar(10))+' and t.id<='+cast(@PageUpperBound as varchar(10))+' order by a.G_G_R_ID desc'
exec(@csql)

[pengdali]

declare @表名 varchar(100 varchar(100)
select @表名='xxx' ,@条件=' where 1=1 and ''a''=''a''' --这里的''转义为'

exec('select * from '+@表名+@条件)

[houjianxun]

OK,大力的方法解决了偶的问题，一会结贴

To: pengdali
能告诉我net_steven给的答案错在哪里了吗？
原来我声明的@PageLowerBound 是int，为什么要再转成varchar呢？

[net_steven]

不好意思,转换函数写错了:
cast(@PageLowerBound as int)->
cast(@PageLowerBound as varchar(10))

[pengdali]

应为:
declare @a int
set @a=1
select 1 where 1=@a

改为动态sql:
declare @a int
set @a=1
exec('select 1 where 1='+@a)
-----------------------^^^^^直接这样写是会错的，SQLServer会自动转换，但不是把@a变为varchar而是把“select * from 表 where 1=”字符串转换为int，这样当然错了
所以不用用默认的隐式转换，而必须用cast转换，改为：

declare @a int,@sqlstring varchar(8000)
set @a=1
set @sqlstring='select 1 where 1='+cast(@a as varchar(10))
exec(@sqlstring)