存储过程里多条件判断怎么写?紧急中
我存储过程里想实现多个传入参数的判断,里面有7个传入参数条件.
CREATE PROCEDURE sp_tbWasteSource_Search
(
@sd datetime, //开始日期
@ed datetime, //结束日期
@con1 varchar(50),
@con2 varchar(30),
@con3 varchar(5),
@con4 varchar(10),
@con5 varchar(4)
)
AS
declare @sql varchar(1000)
begin
set @sql='SELECT * FROM table '
if @sd!='' and @ed!=''
begin
set @sql=@sql+' where CosID= '+cast(@CosID as varchar)
end
if (@CosName!= '' and @CosID!='')
begin
set @sql=@sql+'and'+' CosName= '+cast (@CosName as varchar )
end
else
if (@CosName!='' and @CosID ='')
begin
set @sql=@sql+'where '+' CosName= '+cast (@CosName as varchar )
end
if @CosCredit!= '' and (@CosID!='' or @CosName!='')
begin
set @sql=@sql+' and CosCredit= '+cast (@CosCredit as varchar )
end
else
if @CosCredit!='' and @CosID=''and @CosName=''
set @sql=@sql+'where CosCredit= '+cast (@CosCredit as varchar )
exec (@sql)
end
GO
以这样的形式来判断，我想实现，假如两个日期条件不输入,那么就以后面5个输入条件来判断。就是相当于以某一个输入条件做查询,假如输入条件了，那么就是以日期条件和5个条件里的某一个作为判断?
[最优解释]
发你代码，解决了 ！
[其他解释]
CREATE PROCEDURE sp_tbWasteSource_Search
(
@sd datetime=null, //开始日期
@ed datetime=null, //结束日期
@con1 varchar(50),
@con2 varchar(30),
@con3 varchar(5),
@con4 varchar(10),
@con5 varchar(4)
)
as
begin
select * from tb
where (@sd is null or date>@sd) and (@ed is null or date<@ed)
end
create table #temp
(
卡号 varchar(100),
等级 nvarchar(100),
积分 int,
升级时间 datetime
)
insert #temp
select '000005', '9折', '3001', '2010-3-8' union all
select '000006', '9折', '3022', '2010-5-9' union all
select '000005', '8.5折', '6008', '2010-7-9' union all
select '000005', '8折', '10009', '2011-2-9' union all
select '000006', '8.5折', '6700', '2011-1-1'
--SQL:
select [9折升级时间] = n.[9折], [9折时积分]=m.[9折], [8.5折升级时间]=n.[8.5折], [8.5折时积分]=m.[8.5折],[8折升级时间]=n.[8折],[8折时积分]=m.[8折] from
(
select * from
(select 卡号, 等级, 积分 from #temp) a
pivot
(max(积分) for 等级 in ([9折], [8.5折], [8折])) b
) m
inner join
(
select * from
(select 卡号, 等级, 升级时间 from #temp) a
pivot
(max(升级时间) for 等级 in ([9折], [8.5折], [8折])) b
) n
on m.卡号 = n.卡号
/*
9折升级时间9折时积分8.5折升级时间8.5折时积分8折升级时间8折时积分
2010-03-08 00:00:00.00030012010-07-09 00:00:00.00060082011-02-09 00:00:00.00010009
2010-05-09 00:00:00.00030222011-01-01 00:00:00.0006700NULLNULL
*/
--如果列不固定,用动态SQL自己拼,CSDN上的例子很多
http://topic.csdn.net/u/20110310/12/ed017dee-c0b9-42f6-b02d-afa222466e9f.html
SELECT * FROM table_name
where ((@sd is null and @ed is null) or (sd >= @sd and ed <= @ed)) --sd和ed都是空时,此条件总为true,否则相当于sd >= @sd and ed <= @ed
and (@con1 is null or con1 = @con1) --如果@con1不传值,则此行条件总为true;如果传值,则此行为and con1=@con1
and (@con2 is null or con2 = @con2) --同con1
and (@con3 is null or con3 = @con3) --同con1
and (@con4 is null or con4 = @con4) --同con1
and (@con5 is null or con5 = @con5) --同con1
SELECT * FROM table
where 1=1
and sd>= case when @sd is not null when @sd else sd end
and ed<= case when @ed is not null when @ed else ed end
and CosID= case when @CosID is not null when @CosID else CosID end
and CosName= case when @CosName is not null when @CosName else CosName end
and .....
