alter proc usp_YeWu_Search
(
@YunDanHao nvarchar(30)=null,
@YeWuYuan int=null,
@KeHu int=null,
@YeWuFaShengRiQi1 date=null,
@YeWuFaShengRiQi2 date=null
)
as
begin
declare @sql nvarchar(max)='
select yw.ID
,yw.YunDanHao
,ry.Name YeWuYuanXingMing
,kh.Name KeHuXingMing
,yslx.Name YunShuLeiXingMingChen
,yw.YeWuFaShengRiQi
,yw.BeiZhu
from YeWu yw
inner join RenYuan ry on ry.ID=yw.YeWuYuan
inner join KeHu kh on kh.ID=yw.KeHu
inner join YunShuLeiXing yslx on yslx.ID=yw.YunShuLeiXing
where (1=1)
'
if @YunDanHao is not null
set @sql=@sql + ' and (yw.YunDanHao=@YunDanHao)'
if @YeWuYuan is not null
set @sql=@sql + ' and (yw.YeWuYuan=@YeWuYuan)'
if @KeHu is not null
set @sql=@sql + 'and (yw.KeHu=@KeHu)'
if (@YeWuFaShengRiQi1 is not null) and (@YeWuFaShengRiQi2 is not null)
set @sql=@sql + ' and (yw.YeWuFaShengRiQi between @YeWuFaShengRiQi1 and @YeWuFaShengRiQi2)'
exec sp_executesql @sql,N'
@YunDanHao nvarchar(30)=null
,@YeWuYuan int=null
,@KeHu int=null
,@YeWuFaShengRiQi1 date=null
,@YeWuFaShengRiQi2 date=null
',
@YunDanHao
,@YeWuYuan
,@KeHu
,@YeWuFaShengRiQi1
,@YeWuFaShengRiQi2
end
declare @sql nvarchar(max)='
select yw.ID
,yw.YunDanHao
,ry.Name YeWuYuanXingMing
,kh.Name KeHuXingMing
,yslx.Name YunShuLeiXingMingChen
,yw.YeWuFaShengRiQi
,yw.BeiZhu
from YeWu yw
inner join RenYuan ry on ry.ID=yw.YeWuYuan'
if @YunDanHao is not null
set @sql=@sql + ' and (ry.ID=@YunDanHao)'
set @sql=@sql + '
inner join KeHu kh on kh.ID=yw.KeHu'
if @KeHu is not null
set @sql=@sql + 'and (kh.ID=@KeHu)'
set @sql=@sql + '
inner join YunShuLeiXing yslx on yslx.ID=yw.YunShuLeiXing'
if (@YeWuFaShengRiQi1 is not null) and (@YeWuFaShengRiQi2 is not null)
set @sql=@sql + ' where (yw.YeWuFaShengRiQi between @YeWuFaShengRiQi1 and @YeWuFaShengRiQi2)'
