日期字段中具有可空值的存储过程
本文关键字:空值 存储过程 字段 日期 | 更新日期: 2023-09-27 18:14:51
在这个存储过程中,在where条件中,如果@startDate和@EndDate值为null,那么如何使用COALESCE处理它。
CREATE PROCEDURE dbo.SP_ManageLeaveSearch
@StartDate datetime,
@EndDate datetime,
@UserName CHAR(100),
@MarketID INT
AS
BEGIN
SELECT d1.UserName,c1.HouseName,c.StartDate,c.EndDate
FROM table1 c
Inner JOIN table2 d1 ON c.UserID = d1.UserID
WHERE
(
(
(@StartDate BETWEEN StartDate AND EndDate)
OR
(@EndDate BETWEEN StartDate AND EndDate)
OR
(
(@StartDate <= Startdate AND @EndDate>=EndDate)
)
)
AND
(
d1.UserName = COALESCE(@UserName, d1.UserName)
)
)
END
提前感谢。
这应该可以为您工作:
CREATE PROCEDURE dbo.ManageLeaveSearch
@StartDate datetime = NULL,
@EndDate datetime = NULL,
@UserName CHAR(100) = NULL,
@MarketID INT
AS
BEGIN
SELECT d1.UserName,c1.HouseName,c.StartDate,c.EndDate
FROM table1 c
Inner JOIN table2 d1 ON c.UserID = d1.UserID
WHERE
COALESCE(@StartDate,StartDate) <= EndDate AND
StartDate <= COALESCE(@EndDate,EndDate) AND
d1.UserName = COALESCE(@UserName, d1.UserName)
END
指出:
- 不要使用
sp_
前缀来命名存储过程——这是为微软的系统过程保留的。 - 我已经用更简单的条件来确定重叠的存在。如果范围1在范围2结束之前开始,和范围2在范围1结束之前开始,两个范围之间存在重叠。
- 我已经为那些应该为空的参数指定了默认值。
试试这个-
CREATE PROCEDURE dbo.usp_ManageLeaveSearch
@StartDate DATETIME,
@EndDate DATETIME,
@UserName CHAR(100),
@MarketID INT
AS BEGIN
SELECT
d1.UserName
, c.HouseName
, c.StartDate
, c.EndDate
FROM dbo.table1 c
JOIN dbo.table2 d1 ON c.UserID = d1.UserID
WHERE d1.UserName = ISNULL(@UserName, d1.UserName)
AND (
ISNULL(@StartDate, StartDate) BETWEEN StartDate AND EndDate
OR
ISNULL(@EndDate, EndDate) BETWEEN StartDate AND EndDate
)
END
你把事情弄得太复杂了。这应该为您工作,是一个简单的查询,我知道。我更喜欢这种语法,因为在我的大多数用例中,…OR @UserName IS NULL可以在执行之前从查询中删除,从而使执行非常简单,快速。
CREATE PROCEDURE dbo.SP_ManageLeaveSearch
@StartDate datetime,
@EndDate datetime,
@UserName CHAR(100),
@MarketID INT
AS
BEGIN
SELECT d1.UserName,c1.HouseName,c.StartDate,c.EndDate
FROM table1 c
JOIN table2 d1
ON c.UserID = d1.UserID
WHERE (@StartDate<=EndDate OR @StartDate IS NULL)
AND (@EndDate>=StartDate OR @EndDate IS NULL)
AND (d1.UserName = @UserName OR @UserName IS NULL)
END