CODEWITHSUNDEEP
sql-server-2008t-sql
crazy novice
crazy novice

Reputation: 1817

anyway of avoiding duplicate code in the if-else branch

Here is the table and data

CREATE TABLE #Customers
(
    ID              INT NOT NULL,
    ProductID       INT NOT NULL,
    OrderID         INT NULL
)

INSERT INTO #Customers VALUES (1,10,1)
INSERT INTO #Customers VALUES (2,10,NULL)
INSERT INTO #Customers VALUES (3,10,5)
INSERT INTO #Customers VALUES (4,20,NULL)
INSERT INTO #Customers VALUES (5,20,NULL)
INSERT INTO #Customers VALUES (6,20,22)
GO

I have a a stored procedure where decision of result is based on a parameter @OrderID. If @OrderID is not null then it returns rows matching this param otherwise return all rows. Something like as follows.

DECLARE @OrderID INT 
SET @OrderID = NULL

IF @OrderID IS NULL 
BEGIN
    SELECT * FROM #Customers
END
ELSE
BEGIN
    SELECT * FROM #Customers
    WHERE OrderID = @OrderID 
END

I was just wondering if there is anyway of writing a WHERE clause that can cover both cases. This way I can avoid duplication of code in the IF-ELSE branch

Upvotes: 0

Views: 60

Answers (2)

Mike Cheel
Mike Cheel

Reputation: 13106

DECLARE @OrderId INT = NULL

SELECT * 
FROM #Customers c
WHERE 1=1
AND (@OrderId IS NULL OR c.OrderId = @OrderId)

If variable @ORderId is NULL all rows will return. If it is not NULL then the second half of the filter will fire.

Upvotes: 1

Sandeep Kumar
Sandeep Kumar

Reputation: 803

you can write it as

        SELECT * FROM #Customers
WHERE (OrderID = @OrderID  or @OrderID is Null)

Upvotes: 1

Related Questions