想写一个存储过程比如说
create procedure test(@where1 int, @where2 int, @where3 int)
as
--当@where1有传值时select * from tabletest where where1 = @where1--当@where1、@where2有传值时
select * from tabletest where where1 = @where1 and where2 = @where2--当@where1、@where2、@where3有传值时
select * from tabletest where where1 = @where1 and where2 = @where2 and where3 = @where3我想要得到的是,在不使用动态sql的情况下,能够做到where后的条件根据那个三个值自动增加条件。因为如果在动态sql中用临时表的话,在动态sql外引用不了这个临时表。
现在不知道怎么处理,各位大侠能不能帮帮忙?
create procedure test(@where1 int, @where2 int, @where3 int)
as
--当@where1有传值时select * from tabletest where where1 = @where1--当@where1、@where2有传值时
select * from tabletest where where1 = @where1 and where2 = @where2--当@where1、@where2、@where3有传值时
select * from tabletest where where1 = @where1 and where2 = @where2 and where3 = @where3我想要得到的是,在不使用动态sql的情况下,能够做到where后的条件根据那个三个值自动增加条件。因为如果在动态sql中用临时表的话,在动态sql外引用不了这个临时表。
现在不知道怎么处理,各位大侠能不能帮帮忙?
as
if @where1 is not null and @where2 is null and @where3 is null
select * from tabletest where where1 = @where1
if @where1 is not null and @where2 is not null and @where3 is null
select * from tabletest where where1 = @where1 and where2 = @where2
if @where1 is not null and @where2 is not null and @where3 is not null
select * from tabletest where where1 = @where1 and where2 = @where2 and where3 = @where3
as
declare @sql nvarchar(2000)
set @sql= 'select * from tabletest where 1=1 '+ @where1
exec (@sql)exec test 'and where1 = @where1 '
exec test 'and where1 = @where1 and where2 = @where2 '
呵,我就是不想用动态sql,
如果动态sql创建的临时表,其它地方也能引用的话,倒是可以用!
select *
from tabletest
where (where1 = @where1 or @where1 is null)
and (where2 = @where2 or @where2 is null)
and (where3 = @where3 or @where3 is null);
select *
from tabletest
where where1 = case when @where1 is not null then @where1 else where1 end
where2 = case when @where2 is not null then @where2 else where2 end
where3 = case when @where3 is not null then @where3 else where3 end
select *
from tabletest
where where1 = case when @where1 is not null then @where1 else where1 end and
where2 = case when @where2 is not null then @where2 else where2 end and
where3 = case when @where3 is not null then @where3 else where3 end
where where1 = @where1
and (where2 = @where2 or @where2 is null)
and (where3 = @where3 or @where3 is null)