在WHERE子句中使条件为可选 [英] Make condition optional in WHERE clause

问题描述

你好，

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

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)

好，您尚未指定必需参数，因此我正在考虑前两个参数作为强制参数

所以您的SP将如下所示

Well , You haven''t specified the Required parameters so I am considering first two parameters as mandatory parameters

So you SP will be as below

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

	 --IF @RequestStatusId = 0 THEN @RequestStatusId=NULL 
         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子句中使条件为可选的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！