加个判断 if @pMax is not null begin select * from table1 where MarketPrice >= @pMin and MarketPrice <= @pMax end else select * from table1 where MarketPrice >= @pMin
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GOCREATE PROC PROC_ABC@pMax float, @pMin float AS SET NOCOUNT ON BEGIN TRAN if @pMax is not null begin select * from table1 where MarketPrice >= @pMin and MarketPrice <= @pMax end IF @@ERROR <> 0 BEGIN ROLLBACK TRAN SELECT -100 RETURN -100 ENDelse select * from table1 where MarketPrice >= @pMin IF @@ERROR <> 0 BEGIN ROLLBACK TRAN SELECT -200 RETURN -200 END COMMIT TRANGO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO------ EXEC PROC_ABC 9,8
select * from table1 where MarketPrice >= @pMin and MarketPrice <= isnull(@pMax,MarketPrice)
呵呵,多谢大家! 如果是日期型 select * from table1 where f_date between isnull(@dBegin,f_date) and isnull(@dEnd,getDate()) 如果是 isnull(@dEnd,getDate() ->isnull(@dEnd,f_date) 就不行了,f_date 是匹配查到的第一条记录吗?
if @dBegin is null set @dBegin=cast('0000-01-01' as datetime) if @dEnd is null set @dEnd=getdate() select * from table1 where f_date between @dBegin and @dEnd
if @pMax is not null
begin
select * from table1 where MarketPrice >= @pMin and MarketPrice <= @pMax
end
else
select * from table1 where MarketPrice >= @pMin
GO
SET ANSI_NULLS ON
GOCREATE PROC PROC_ABC@pMax float,
@pMin float
AS SET NOCOUNT ON
BEGIN TRAN if @pMax is not null
begin
select * from table1 where MarketPrice >= @pMin and MarketPrice <= @pMax
end
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
SELECT -100
RETURN -100
ENDelse
select * from table1 where MarketPrice >= @pMin IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
SELECT -200
RETURN -200
END COMMIT TRANGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO------
EXEC PROC_ABC 9,8
呵呵,多谢大家!
如果是日期型
select * from table1 where f_date between isnull(@dBegin,f_date) and isnull(@dEnd,getDate()) 如果是 isnull(@dEnd,getDate() ->isnull(@dEnd,f_date) 就不行了,f_date 是匹配查到的第一条记录吗?
set @dBegin=cast('0000-01-01' as datetime)
if @dEnd is null
set @dEnd=getdate()
select * from table1 where f_date between @dBegin and @dEnd