CODEWITHSUNDEEP
sqlsql-servert-sql
Vidya Nand Tiwari
Vidya Nand Tiwari

Reputation: 1

T-SQL Case in Where issue

I tried to search trough several bolgs however,I am still struggling with the issue in SQL case in Where Clause. Could someone please help me out? Below is the scenario-

I have a table Client_Master, I have to create a stored proc to display:

  1. All companies from all clients when user clicks on All radio button
  2. All companies specific to Only Client1 when user clicks on Client1 radio Button
  3. All companies specific to Only Client2 when user clicks on Client2 radio Button

I tried writing SQL code as -

CREATE PROCEDURE ClientSearchList
--Exec ClientSearchList
AS
    SET NOCOUNT OFF
BEGIN
Declare @Filter Varchar(10)
Set @Filter = Null
SELECT DISTINCT client_Name FROM M_CLIENT_MASTER
Where case 
WHEN @Filter = 'ALL' THEN Client_id <> 0 AND Activate = 0
WHEN @Filter = 'TATA' THEN Client_id <> 0 and Activate = 0 and (Client_Name like '%Tata%' OR   Client_Type=9)
WHEN @Filter = 'CONCEPT' THEN

IF @@ERROR <> 0 GOTO ERRORHANDLER**
    SET NOCOUNT OFF
    RETURN(0)

ERRORHANDLER:
    RETURN(@@ERROR)
END

Please suggest a way to get this working.

Thanks, Vidya

Upvotes: 0

Views: 131

Answers (2)

Kadir Turan
Kadir Turan

Reputation: 133

Maybe this can work for u

Select 
ProductID
OrderNo,
OrderType,
OrderLineNo
From Order_Detail
Where ProductID in (
Select Case when (@Varibale1 != '') 
then (Select ProductID from Product P Where .......)
Else (Select ProductID from Product)
End as ProductID
)

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

I don't know why people new to SQL seem to reach for CASE1 when all they need are boolean operators - the same type of operators they surely use in other languages:

Where
   (@Filter = 'ALL' AND Client_id <> 0 AND Activate = 0) OR
   (@Filter = 'TATA' AND Client_id <> 0 and Activate = 0 and
        (Client_Name like '%Tata%' OR Client_Type=9)) OR
   (@Filter = 'CONCEPT' AND ... )

Question leaves CONCEPT branch incomplete, so answer does too. Also, those first two branches look to have some similarities, so could be simplified:

Where
   (Client_id <> 0 AND Activate = 0 AND
       (@Filter = 'ALL' OR
           (@Filter = 'TATA' AND
               (Client_Name like '%Tata%' OR Client_Type=9)
           )
       )
   ) OR
   (@Filter = 'CONCEPT' AND ... )

1 CASE is an expression - it computes a single value. It's not some form of control flow. There aren't any such control flow constructs for use within a SELECT statement.

Upvotes: 6

Related Questions