CODEWITHSUNDEEP
sql-serverstored-proceduresconditional-statementswhere-clause
Armaan Labib
Armaan Labib

Reputation: 165

Put where clause criteria in SQL based on conditions?

I have a stored procedure with two parameters which is returning me the list of employees. My stored procedure is working fine but I need to put criteria in my where clause based on conditions. Following is my stored procedure:

create procedure sp_GetEmployees

   @BranchId int = 0,
   @DeptId int = 0

as

Begin

 if(@BranchId > 0 and @DeptId > 0)
  select * from Employees where BranchId = @BranchId and DeptId = @DeptId
 else if (@BranchId > 0 and @DeptId = 0)
  select * from Employees where BranchId = @BranchId
 else if (@BranchId > 0 and @DeptId = 0)
  select * from Employees where DeptId = @DeptId
 else
  select * from Employees

End

This is simple case but I have more complex scenarios where I am passing 8 to 10 parameters and making conditions for them will be big headache. I need to simplify it in one select statement. I tried the following but of course, it is not correct:

if(@BranchId > 0 or @DeptId > 0)
select * from Employees where
     case @BranchId when 0 then ''
     else BranchId = @BranchId 
     End
else
select * from Employees

How can I simplify it ?

Upvotes: 0

Views: 603

Answers (4)

Gajjar Shalin
Gajjar Shalin

Reputation: 95

You can concatenate your query with condition check like this way :

ALTER PROCEDURE [dbo].[Your stored procedure name]  
 @aaa  INT condition parameter,  
 @bbb INT condition parameter,  
 @ccc INT condition parameter,  
 @RecordCount INT OUTPUT   
AS   

Declare @aaa nvarchar(500)  
Declare @bbb nvarchar(500)  
Declare @ccc nvarchar(500)  
BEGIN  
 BEGIN TRY  

  SET @qry = N'SELECT <here your query> where  1=1  
     '  
  IF @aaa= 0  
  BEGIN  
   SET @aaa = ''  
  END  
  ELSE  
  BEGIN  
   SET @aaa = ' and p.userid = '+CONVERT(varchar(5),@aaa)+' '  
  END  

  IF @bbb= 0  
  BEGIN  
   SET @bbb = ''  
  END  
  ELSE  
  BEGIN  
   SET @bbb = ' and p.Senderid = '+CONVERT(varchar(10),@bbb)+' '  
  END

Upvotes: 0

Oleg Bondarenko
Oleg Bondarenko

Reputation: 1820

You could do something like this: 

WHERE (@Parameter1 = 0 OR Field1=@Parameter1)
AND (@Parameter2 = 0 OR Field2=@Parameter2)
AND ...

Upvotes: 1

Thom A
Thom A

Reputation: 95561

I would go with this, adding the OPTION (RECOMPILE) to your query; this means that any Cached query plans won't be used, as they could be useless to a different set of parameters (with this being a catch-all query):

CREATE PROCEDURE sp_GetEmployees @BranchId int = 0,
                                 @DeptId int = 0
AS
BEGIN

    SELECT *
    FROM Employees
    WHERE (BranchId = @BranchId
        OR @BranchId = 0)
      AND (DeptId = @DeptId
        OR @DeptId = 0)
    OPTION (RECOMPILE);

END;

You were trying to use the CASE expression like a statement, which is it not.

Upvotes: 1

PSK
PSK

Reputation: 17943

You can simplify your query like following.

SELECT * 
FROM   Employee 
WHERE  ( Branchid = @BranchId 
          OR @BranchId = 0 ) 
       AND ( Deptid = @DeptId 
              OR @DeptId = 0 )

Upvotes: 1

Related Questions