CODEWITHSUNDEEP
sqlsql-server
IUnknown
IUnknown

Reputation: 9819

Dynamic AND clause

Within a stored proc ,I want to include an AND clause in a query only if a particular condition is satisfied.

INSERT INTO #firstResults   
 SELECT x, y, z  FROM ep  
 WHERE ep.proposedBy = @Id  
 AND ep.pId IN ( SELECT DISTINCT pId FROM #tempProj)

The 'AND' clause should join in only when the #tempProj is not empty.
What is the most elegant way to implement this?

I am on sql server 2008.
Since this would be repeated several times over the proc,I wish to avoid branching IF statements.

Upvotes: 0

Views: 74

Answers (3)

Raj
Raj

Reputation: 10853

IF EXISTS (SELECT TOP 1 NULL FROM #tempProj)
BEGIN
INSERT INTO #firstResults   
 SELECT x, y, z  FROM ep  
 WHERE ep.proposedBy = @Id  
 AND ep.pId IN ( SELECT DISTINCT pId FROM #tempProj)
END
ELSE
BEGIN
 INSERT INTO #firstResults   
 SELECT x, y, z  FROM ep  
 WHERE ep.proposedBy = @Id  
END

Upvotes: 0

twrowsell
twrowsell

Reputation: 467

Try this ...

AND (ep.pId IN (SELECT DISTINCT pId FROM #tempProj) 
                OR (SELECT COUNT(*) FROM #tempProj) = 0))

Upvotes: 2

M.Ali
M.Ali

Reputation: 69554

IF EXISTS (SELECT * FROM #tempProj)
 BEGIN
    INSERT INTO #firstResults   
    SELECT x, y, z  FROM ep  
    WHERE ep.proposedBy = @Id  
    AND ep.pId IN ( SELECT DISTINCT pId FROM #tempProj)
 END
ELSE
  BEGIN
    INSERT INTO #firstResults   
    SELECT x, y, z  FROM ep  
    WHERE ep.proposedBy = @Id 
  END

Upvotes: 1

Related Questions