要求:在WHERE中参数上加入Function判定,实现没有传值的情况下无视单元格输出数据;
--格式如下--
exec PROCEDURE @pcid,@year,@month
GO
CREATE procedure ...
@pcid int,
@year int,
@month int
as
Begin
select [..],[..],...
from [...]
where 1=1
and [...].[Libby_IsNull_RETURN_decimal]('=',year(...),@year)=1
and [...].[Libby_IsNull_RETURN_decimal]('=',month(...),@month)=1
End
GO
CREATE FUNCTION [Libby_IsNull_RETURN_decimal]
(
@TYPE varchar(10),
@O_1 decimal(18,3),
@O_2 decimal(18,3)
)
RETURNS int
AS
BEGIN
-- 空值为1,不为空判断
DECLARE @RE_SET AS int
set @RE_SET=1
if(@TYPE='=')
if(@O_1 = @O_2)set @RE_SET=1 else set @RE_SET=0
if(@TYPE='>')
if(@O_1 > @O_2)set @RE_SET=1 else set @RE_SET=0
if(@TYPE='>=')
if(@O_1 >= @O_2)set @RE_SET=1 else set @RE_SET=0
if(@TYPE='<')
if(@O_1 < @O_2)set @RE_SET=1 else set @RE_SET=0
if(@TYPE='<=')
if(@O_1 <= @O_2)set @RE_SET=1 else set @RE_SET=0
if(@O_1 is null) set @RE_SET=1
if(@O_2 is null) set @RE_SET=1
return @RE_SET
END
GO
注:Function内我写了当二个判断其一为Null便可通过的写法(以公司的数据结构设计和),请结合自身公司的情况来编写;