如果第一个为空，如何使用其他参数值 [英] How to use other parameter value if the first is empty

问题描述

我有一个SQL查询，我将传递第一个或第二个参数。如果first为空/ null，那么我将在WHERE子句中使用第二个参数值。但我有一个困惑，我如何在我的WHERE子句中编写我的查询以满足我的情况？

以下是我的脚本：

Hi, I have a SQL query in which I will pass either first or second parameter. If first is empty/null, then I will use second parameter value in my WHERE clause. But I have one confusion, how can I write my query in my WHERE clause to cater my case here?
Below are my script:

DECLARE @p_CustomerName AS VARCHAR(20)
DECLARE @p_CustomerID AS VARCHAR(20)

SET @p_CustomerName = NULL
SET @p_CustomerID = '123ABC'

SELECT
 FirstName
,LastName
FROM
CustomerTable AS CT
WHERE
--Doubts here
--CustomerName and CustomerID consists in 2 different columns, therefore OR clause is not suitable here 

我尝试了什么：



我知道我可以在这里使用IF ELSE声明，但是会有2个查询。我试图在一个查询中实现它。

What I have tried:

I know I can use IF ELSE statement here, but there would be 2 queries. I am trying to achieve it in a single query.

推荐答案

试试这个：

Try this:

SELECT FirstName, LastName FROM customertable WHERE
(CustomerName=@p_CustomerName OR @p_CustomerName IS NULL) AND
(CustomerID=@p_CustomerID OR @p_CustomerID IS NULL)

您好，



试试这个：

Hello,

try this :

SELECT FirstName, LastName FROM CustomerTable AS CT
WHERE
CustomerName = CASE WHEN ISNULL(@p_CustomerName, '') = '' THEN CustomerName ELSE ISNULL(@p_CustomerName, '') END
AND CustomerID = CASE WHEN ISNULL(@p_CustomerID, '') = '' THEN CustomerID ELSE ISNULL(@p_CustomerID, '') END

这篇关于如果第一个为空，如何使用其他参数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！