3

I am trying to do a SQL query and to build the where condition dynamically depending if the parameters are null or no. I have something like this:

SELECT tblOrder.ProdOrder, tblOrder.Customer FROM tblOrder 
CASE WHEN @OrderId IS NOT NULL
THEN 
WHERE tblOrder.OrderId = @OrderId
ELSE
END
CASE WHEN @OrderCustomer IS NOT NULL
THEN
AND tblOrder.OrderCustomer = @OrderCustomer
ELSE
END
END

This doesn't work, but this is just a small prototype how to assemble the query, so if the orderid is not null include in the where clause, or if the ordercustomer is not null include in the where clause. But I see problem here, for example if the ordercustomer is not null but the orderid is null, there will be error because the where keyword is not included.

How can I tackle this problem?

Improve this question

3 Answers 3

Reset to default
4

This should do what you want:

SELECT tblOrder.ProdOrder, tblOrder.Customer 
FROM tblOrder 
WHERE ( @OrderId IS NULL OR  tblOrder.OrderId = @OrderId )
AND   ( @OrderCustomer IS NULL OR  tblOrder.OrderCustomer = @OrderCustomer )
OPRION (RECOMPILE)

But as commented you should include the OPTION RECOMPILE hint, otherwise it will have bad performance.

Worth reading:

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

But have poor performance unless OPTION(RECOMPILE) is used
This works, but it cannot leverage indexes. Dynamic SQL with sp_ExecuteSql is probably a better choice, in general, for this kind of problem, as Gail Shaw explains here: sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries
@MartinSmith (noob side question): how do you embed your link within text like that in a comment? I can't edit other people's comments, so I can't figure out how they do it...
1

You can't dynamically write the WHERE clause, but you can use compound statements to achieve the desired effect. Since in SQL NULL is never equal to any value, you can actually achieve a pretty elegant query:

SELECT tblOrder.ProdOrder, tblOrder.Customer 
FROM   tblOrder 
WHERE  
-- Can only be true if @OrderId isn't NULL, no need to state it explicitly
tblOrder.OrderId = @OrderId 
OR 
-- Thanks to short-circuit evaluation, 
-- we only get here if the first condition evaluated as false
tblOrder.OrderCustomer = @OrderCustomer
Improve this answer

1 Comment

If the parameters are NULL they shoul be ignored in the filter.
0

This query is completely wrong. After formatting you can see that there are "blank" ELSE statements, and you have extra END at the end :):

SELECT 
    tblOrder.ProdOrder, tblOrder.Customer 
FROM tblOrder 
CASE 
    WHEN @OrderId IS NOT NULL THEN WHERE tblOrder.OrderId = @OrderId
    ELSE 
END
CASE 
    WHEN @OrderCustomer IS NOT NULL THEN AND tblOrder.OrderCustomer = @OrderCustomer
    ELSE 
END
END

Besides you can't return WHERE clause from CASE.

Use this instead. It's much more simple, and it works ;).

SELECT 
    tblOrder.ProdOrder, tblOrder.Customer 
FROM tblOrder 
where
    tblOrder.OrderId = @OrderId
    OR 
    tblOrder.OrderCustomer = @OrderCustomer

You don't even have to worry about both variables being NULL, if you have ANSI_NULLS set. By default Sql Server won't match records, where NULL=NULL.

Improve this answer

1 Comment

Ignores the fact that the parameters can be NULL in which case they should be ignored in the filter.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.