我在执行存储过程时使用WHERE
子句中的函数dbo.Split
。我不想在输入参数NULL
时执行此功能。
例如
Create Procedure [dbo].[HRMS_RPT_CategoryWiseSeniorityList]
@fk_BranchRegion_ID bigint = null,
@pk_BranchType_ID nvarchar(100) = null
AS
BEGIN
Select distinct SR.fk_branchType_ID
from HRMS_Employee_ServiceRecord SR (nolock)
Inner Join HRMS_Mst_Branch B (nolock) On B.pk_Branch_ID = SR.fk_Branch_ID
Where
B.fk_BranchRegion_ID = IsNull(@fk_BranchRegion_ID,B.fk_BranchRegion_ID) --Region Condition
AND SR.fk_BranchType_ID in (SELECT * from dbo.Split( @pk_BranchType_ID,','))
END
我使用以下SQL在NULL
时忽略SR.fk_BranchType_ID
上的过滤器@pk_BranchType_ID
:
SR.fk_BranchType_ID = IsNull(@pk_BranchType_ID, SR.fk_BranchType_ID)
我想为@pk_BranchType_ID
做类似的事情. 如果我像使用@pk_BranchType_ID
那样使用ISNULL
,则不会返回任何行。当它NULL
.
如何让我的存储过程在NULL
时忽略SR.fk_BranchType_ID
上的过滤器@pk_BranchType_ID
?
如果要
在输入参数为 NULL 时删除筛选器,可以在 WHERE
子句中使用 NULL 检查,并结合如下OR
:
Create Procedure [dbo].[HRMS_RPT_CategoryWiseSeniorityList]
@fk_BranchRegion_ID bigint = null,
@pk_BranchType_ID nvarchar(100) = null
AS
BEGIN
Select
distinct SR.fk_branchType_ID
FROM HRMS_Employee_ServiceRecord SR WITH(nolock)
INNER JOIN HRMS_Mst_Branch B WITH(nolock)
ON B.pk_Branch_ID = SR.fk_Branch_ID
WHERE B.fk_BranchRegion_ID = IsNull(@fk_BranchRegion_ID,B.fk_BranchRegion_ID) --Region Condition
-- Remove filter on @pk_BranchType_ID when it is NULL
AND ( @pk_BranchType_ID IS NULL
OR SR.fk_BranchType_ID in (SELECT * from dbo.Split( @pk_BranchType_ID,',')) )
END
您可能希望将参数设置为null
如下所示:
@fk_BranchRegion_ID bigint is Null,
@pk_BranchType_ID nvarchar(100) is Null