Set up conditional restriction in SP Query? 1

[Amesville]

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

 

[Qik3Coder]

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 dbrders 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)

 

[markros]

IF exists (select 1 FROM TableY WHERE E = @Par1)

 SELECT A, B, C FROM TableX WHERE E = @Par1; 
ELSE
 SELECT A, B, C FROM TableX

PluralSight Learning Library

 

[Amesville]

Thanks, that should do it! Very much appreciated.

Craig