CODEWITHSUNDEEP
sql
PassionateDeveloper
PassionateDeveloper

Reputation: 15148

If Where in stored procedure

i have a stored procedure in which I have a variable which can be null or set for an ID. I want to have a where clause only if the variable is not null.

Here is my code:

DECLARE @managerID int;
SET @managerID = null;
SET @managerID = 6 //This value will come by parameter through stored procude
Select * from Provision 
where manager = @managerID //This should only be here, when the managerID is not null

Upvotes: 0

Views: 38

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

You can do:

where manager = @managerID or @ManagerId is null

However, if you are doing this from an application, you might want to construct the where clause based on the conditions. Database optimizers have a hard time with or in the from clause, so it is better to just have a list of conditions connect by and.

Upvotes: 2

Dgan
Dgan

Reputation: 10295

@managerID will return true if it is null and it will return all rows

DECLARE @managerID int;
SET @managerID = null;
SET @managerID = 6 
Select * from Provision 
where manager = @managerID OR @managerID is null

Upvotes: 0

Related Questions