CODEWITHSUNDEEP
sqlsql-servert-sqlwhere-clause
KWallace
KWallace

Reputation: 1700

SQL Server stored procedure dynamic SQL in WHERE clause

I have written a stored procedure that accepts one parameter. 

Request_ID int = 0

Later, in my WHERE clause, I need to accomplish the following.

I don't know how to describe it, but you can see what I'm trying to do with this completely made up pseudo-code:

WHERE isNull(a.Target_Department, '') <> ''
  AND isNull(a.Resolved_Date, '') = ''
  AND isNull(c.Request_Archived, '') <> 'Y'
  AND IF @Request_ID = 0 
        THEN Request_ID > 0 
        ELSE Request_ID = @Request_ID
    END

Basically, if a specific request_ID is supplied, I want the WHERE clause to narrow to only that specific Request_ID. Otherwise, I want all of them.

Any ideas on how to accomplish this?

Upvotes: 0

Views: 80

Answers (2)

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

and isnull(nullif(@RequestId,0),RequestId)=RequestId

Upvotes: 1

Ross Bush
Ross Bush

Reputation: 15155

Try this -->

WHERE
    (@Request_ID IS NULL OR Request_ID=@RequestID)

OR

WHERE
    (@Request_ID=0 OR Request_ID=@RequestID)

Upvotes: 4

Related Questions