declare @fs nvarchar(4000) set @fs='' if @a<>'' set @fs='a='''+@a+''''if @b<>'' begin if len(@fs)>0 set @fs=@fs+' and ' set @fs=@fs+'b='''+@b+'''' end --我一般就这样做.... if len(@fs)>0 set @fs=' where '+@fs exec('select a,b,c,d from tablea '+@fs)如果是这样用我会在应用程序中先生成符串再作为参数传入
SELECT a, b, c,d FROM tablea
Where @a+@b+.... ^^^^^^^^^^^想必各位老大都知道偶的意思吧。不过上面的存储过程肯定不对,我就是想问一下怎么实现这个功能?:)楼主中的语句就是少了 and 条件
用动态,注意数据类型CREATE PROCEDURE GetResInfo --注意数据类型 @a int =null, @b VARCHAR(256)=null, @c datetime=null, @d numeric(10,2)AS declare @sql varchar(8000) set @sql='' if @a is not null BEGIN SET @sql=@sql +' and a='+cast(@a as varchar(20)) ENDif @b is not null BEGIN SET @sql=@sql +' and b='''+@b+'''' ENDif @c is not null BEGIN SET @sql=@sql +' and c='''+cast(@c as varchar(20))+'''' ENDif @d is not null BEGIN SET @sql=@sql +' and a='+cast(@d as varchar(20)) ENDif @sql='' SELECT a, b, c,d FROM tablea else begin set @sql='SELECT a, b, c,d FROM tablea Where '+right(@sql,len(@sql)-4) exec (@sql) endgo
To 楼上的各位尤其是rea1gz(冒牌realgz V0.1) 诸位的思路我已明白,但是这样依然还有个问题:语句的执行似乎各位老大都是用exec (@sql),那么如何将这个检索结果存储到一个临时表中呢? 一般情况下可用 insert @t_table ( a,b,c,d ) SELECT a,b,c,d FROM ... WHERE ...但是如果使用exec (@sql)恐怕就不好办了吧?采用 insert @t_table ( a,b,c,d ) exec (@sql) 这个语句肯定运行不了吧?
CREATE PROCEDURE GetResInfo @a NVARCHAR(256), @b NVARCHAR(256), @c NVARCHAR(256), @d NVARCHAR(256) AS set nocount on declare @s nvarchar(4000) select @s =case isnull(@a,'') then '' else ' or a=@a' end +case isnull(@b,'') then '' else ' or a=@b' end +case isnull(@c,'') then '' else ' or a=@c' end +case isnull(@d,'') then '' else ' or a=@d' end ,@s='insert #t select * from tablea' +case @s when '' then '' else ' where '+stuff(@s,1,4,'') end--生成临时表结构 select * into #t from tablea where 1<>1--执行查询语句,得到结果 exec sp_executesql @s ,N'@a NVARCHAR(256), @b NVARCHAR(256), @c NVARCHAR(256), @d NVARCHAR(256)' ,@a,@b,@c,@d--显示结果 select * from #t go
用临时表,不要表变量生成表结构那个,也可以直接用create table #t(...)
楼上老大的回答实在是太博大精深了,一看就是个老手。:D 不过俺是个菜鸟,能不能给俺解释一下啊?尤其是: select @s =case isnull(@a,'') then '' else ' or a=@a' end +case isnull(@b,'') then '' else ' or a=@b' end +case isnull(@c,'') then '' else ' or a=@c' end +case isnull(@d,'') then '' else ' or a=@d' end ,@s='insert #t select * from tablea' +case @s when '' then '' else ' where '+stuff(@s,1,4,'') end竟然还有 select @s= 这种用法??set @s= ? stuff(@s,1,4,'')是什么用意?
set @fs=''
if @a<>''
set @fs='a='''+@a+''''if @b<>''
begin
if len(@fs)>0
set @fs=@fs+' and '
set @fs=@fs+'b='''+@b+''''
end --我一般就这样做....
if len(@fs)>0
set @fs=' where '+@fs
exec('select a,b,c,d from tablea '+@fs)如果是这样用我会在应用程序中先生成符串再作为参数传入
Where @a+@b+....
^^^^^^^^^^^想必各位老大都知道偶的意思吧。不过上面的存储过程肯定不对,我就是想问一下怎么实现这个功能?:)楼主中的语句就是少了 and 条件
@a int =null,
@b VARCHAR(256)=null,
@c datetime=null,
@d numeric(10,2)AS
declare @sql varchar(8000)
set @sql=''
if @a is not null
BEGIN
SET @sql=@sql +' and a='+cast(@a as varchar(20))
ENDif @b is not null
BEGIN
SET @sql=@sql +' and b='''+@b+''''
ENDif @c is not null
BEGIN
SET @sql=@sql +' and c='''+cast(@c as varchar(20))+''''
ENDif @d is not null
BEGIN
SET @sql=@sql +' and a='+cast(@d as varchar(20))
ENDif @sql=''
SELECT a, b, c,d FROM tablea
else
begin
set @sql='SELECT a, b, c,d FROM tablea Where '+right(@sql,len(@sql)-4)
exec (@sql)
endgo
一般情况下可用
insert @t_table
(
a,b,c,d
)
SELECT a,b,c,d
FROM ...
WHERE ...但是如果使用exec (@sql)恐怕就不好办了吧?采用
insert @t_table
(
a,b,c,d
)
exec (@sql)
这个语句肯定运行不了吧?
@a NVARCHAR(256),
@b NVARCHAR(256),
@c NVARCHAR(256),
@d NVARCHAR(256)
AS
set nocount on
declare @s nvarchar(4000)
select @s
=case isnull(@a,'') then ''
else ' or a=@a' end
+case isnull(@b,'') then ''
else ' or a=@b' end
+case isnull(@c,'') then ''
else ' or a=@c' end
+case isnull(@d,'') then ''
else ' or a=@d' end
,@s='insert #t select * from tablea'
+case @s when '' then ''
else ' where '+stuff(@s,1,4,'') end--生成临时表结构
select * into #t from tablea where 1<>1--执行查询语句,得到结果
exec sp_executesql @s
,N'@a NVARCHAR(256),
@b NVARCHAR(256),
@c NVARCHAR(256),
@d NVARCHAR(256)'
,@a,@b,@c,@d--显示结果
select * from #t
go
不过俺是个菜鸟,能不能给俺解释一下啊?尤其是:
select @s
=case isnull(@a,'') then ''
else ' or a=@a' end
+case isnull(@b,'') then ''
else ' or a=@b' end
+case isnull(@c,'') then ''
else ' or a=@c' end
+case isnull(@d,'') then ''
else ' or a=@d' end
,@s='insert #t select * from tablea'
+case @s when '' then ''
else ' where '+stuff(@s,1,4,'') end竟然还有 select @s= 这种用法??set @s= ?
stuff(@s,1,4,'')是什么用意?