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