Stored Procedure using Where with if else

Question

I have been trying to use stored procedure for searching data.

ALTER PROCEDURE [dbo].[SP_Something]
(
@Search_Text VARCHAR(4000),
@FILTER INT
)
AS
BEGIN  
SELECT  Customer_Information

FROM Customer
LEFT OUTER JOIN Something K ON  K.Something _Id= Something_Id2

LEFT OUTER JOIN  Something3 KS ON KS.Something_Id =Something_Id3

WHERE

// If @FILTER=1 i want to search below

 Customer_İnformation LIKE '%' + @Search_Text + '%'

// If @FILTER=2 i want to search below

Customer_name LIKE '%' + @Search_Text + '%'

// If @FILTER=3 i want to search below

Customer_Adress LIKE '%' + @Search_Text + '%'

How can i use Where with If conditions ?

Any help will be appreciated

Thank you.

use case when then ... else logic

Milen
– Milen

2013-11-08 08:34:18 +00:00
Commented Nov 8, 2013 at 8:34 — Milen
– Milen, Commented Nov 8, 2013 at 8:34

Giannis Paraskevopoulos · Accepted Answer · 2013-11-08 08:34:15Z

2

ALTER PROCEDURE [dbo].[SP_Something]
(
    @Search_Text VARCHAR(4000),
    @FILTER INT
)
AS
BEGIN  
    SELECT  Customer_Information
    FROM    Customer
    LEFT OUTER JOIN Something K 
            ON  K.Something _Id= Something_Id2
    LEFT OUTER JOIN  Something3 KS 
            ON KS.Something_Id =Something_Id3
    WHERE
        (Filter = 1 AND Customer_İnformation LIKE '%' + @Search_Text + '%')
        OR
        (Filter = 2 AND Customer_name LIKE '%' + @Search_Text + '%')
        OR
        (Filter = 3 AND Customer_Adress LIKE '%' + @Search_Text + '%')
END

answered Nov 8, 2013 at 8:34

Giannis Paraskevopoulos

18.5k2 gold badges52 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mithrandir · Accepted Answer · 2013-11-08 08:42:34Z

0

ALTER PROCEDURE [dbo].[SP_Something]
(
    @Search_Text VARCHAR(4000),
    @FILTER INT
)
AS
BEGIN  
  SELECT  Customer_Information
  FROM Customer
    LEFT OUTER JOIN Something K ON  K.Something _Id= Something_Id2
    LEFT OUTER JOIN  Something3 KS ON KS.Something_Id =Something_Id3
  WHERE
  CASE @FILTER 
     WHEN 1 THEN Customer_İnformation
     WHEN 2 THEN Customer_Name
     WHEN 3 THEN Customer_Address
  END LIKE '%' + @Search_Text + '%';
END

edited Nov 8, 2013 at 8:42

answered Nov 8, 2013 at 8:37

Mithrandir

25.5k6 gold badges53 silver badges67 bronze badges

Collectives™ on Stack Overflow

Stored Procedure using Where with if else

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related