动态执行存储过程条件处理

动态执行条件处理

create procedure test(@where1 nvarchar(2000))
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 '

//

create procedure test(@where1 int, @where2 int, @where3 int)
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

  
//

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 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