存储过程:
create procedure sp_1 @name varchar(20)=null,@d1 datetime=null,@d2 datetime=null
as
select *
from t1
where (@name is null or name=@name) and
(@d1 is null or [date]>=@d1) and
(@d2 is null or [date]<=@d2)
create procedure sp_1 @name varchar(20)=null,@d1 datetime=null,@d2 datetime=null
as
select *
from t1
where (@name is null or name=@name) and
(@d1 is null or [date]>=@d1) and
(@d2 is null or [date]<=@d2)
@name varchar(10),
@s_date datetime,
@e_date datetime
as
begin
declare @s varchar(8000)
set @s = 'select * from t1 where 1 = 1'
if @name is not null
set @s = @s + ' and name ='''+@name+''''
if @s_date is not null
set @s = @s + ' and date >='''+@s_date+''''
if @e_date is not null
set @s = @s + ' and date <='''+@e_date+''''
exec(@s)
end
@name varchar(10),
@s_date datetime,
@e_date datetime
as
select
*
from
t1
where
name=(case when @name is null then name esle @name end)
and
date>=(case when @s_date is null then date esle @s_date end)
and
date<=(case when @e_date is null then date esle @e_date end)
go
declare @date1 datetime
declare @date2 datetime
select * from A
where name=@name and
date>isnull(@date1, 0) and
date<isnull(@date2, 2050-12-30) --这里足够大吧
select *
from t1
where name=@name and (@d1 is null or [date]>=@d1) and (@d2 is null or [date]<=@d2)