Hi Folks

I'm trying to write a stored proc that will return a filtered result if the result of a subquery has data in it, or unfiltered if it doesn't.

So basically it would be SELECT A, B, C FROM TableX;

but if a subquery (SELECT D FROM TableY WHERE E = @Par1;) returns rows, the query should read:

SELECT A, B, C FROM TableX WHERE C IN (SELECT D FROM TableY WHERE E = @Par1);

...But I am not sure how to set this up!

Can anyone offer some help? Thanks.

Craig

Need a little more clarity, so I'm going to use a concrete example.

Select C.*
from dbo.Customers c
WHERE
c.ID in (Select o.CustomerID from dbo.Orders o where o.CustomerID = @CustomerID)
--This is will then select ALL customers
OR @CustomerID is null

Let me know if that doesn't make sense.
You can add a conditional statement, with an

if(@CustomerID is null)
BEGIN
SELECT ...
END
ELSE
BEGIN
SELECT ...
END

Lodlaiden

You've got questions and source code. We want both!
Oh? That? That's not an important password. - IT Security Admin (pw on whiteboard)

PluralSight Learning Library