在WHERE子句中使条件为可选

问题描述

你好，

我正在将五个参数传递给存储过程，其中两个是必需参数，其他所有参数都是可选的.

Hello,

I am passing five parameters to a stored procedure, among which 2 are required and all other are optional.

ALTER PROCEDURE [dbo].[proc_GetUserRequests]
(
	 @EmpUserId INT,
	 @RequestTypeId INT,
	 @RequestStatusId INT=NULL,
	 @StartDate	datetime=NULL,
	 @EndDate datetime=NULL
)
AS

BEGIN
	SELECT Id, Username, SenderAddress, ReceiverAddress, AddedOn, ModifiedOn
	FROM Requests
	WHERE
		EmpUserId = @EmpUserId
		AND RequestTypeId=@RequestTypeId
		AND RequestStatusId=(CASE WHEN @RequestStatusId IS NULL THEN RequestStatusId ELSE @RequestStatusId END)
		AND AddedOn	BETWEEN
		CONVERT(DATETIME, @StartDate+''00:00:00'',120) and
		CONVERT(DATETIME,@EndDate+''23:59:59'',120)

END

如果我将@RequestStatusId传递为null，则我的代码将无法正常工作.
我也希望当@RequestStatusId为零时它是最优的

My code is not working if I pass @RequestStatusId as null.
I also want it to be optinal when @RequestStatusId is zero

Thanks

推荐答案

EmpUserId = @EmpUserId
		AND RequestTypeId=@RequestTypeId
		AND RequestStatusId=(CASE WHEN @RequestStatusId IS NULL or @RequestStatusId=0 THEN RequestStatusId ELSE @RequestStatusId END)
		AND AddedOn	BETWEEN
		CONVERT(DATETIME, @StartDate+''00:00:00'',120) and
		CONVERT(DATETIME,@EndDate+''23:59:59'',120)

ALTER PROCEDURE [dbo].[proc_GetUserRequests]
(
	 @EmpUserId INT,
	 @RequestTypeId INT,
	 @RequestStatusId INT=NULL,
	 @StartDate	datetime=NULL,
	 @EndDate datetime=NULL
)
AS

BEGIN

	 
         IF @RequestStatusId = 0 SET @RequestStatusId=NULL
	 SELECT @StartDate = @StartDate + '00:00:00'
	 SELECT @EndDate=@EndDate + '23:59:59'

	SELECT Id, Username, SenderAddress, ReceiverAddress, AddedOn, ModifiedOn
	FROM Requests
	WHERE
		EmpUserId = @EmpUserId
		AND RequestTypeId=@RequestTypeId
		AND RequestStatusId=COALESCE(@RequestStatusId,RequestStatusId)
		AND AddedOn	BETWEEN COALESCE(@StartDate,AddedOn) AND  COALESCE(@EndDate,AddedOn)


END

Use below code

CASE WHEN isnull(@RequestStatusId,0)=0 THEN

Change your query with this. it solves your problem.

这篇关于在WHERE子句中使条件为可选的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！