CODEWITHSUNDEEP
sqlsql-serverstored-procedurescasewhere-clause
maimok
maimok

Reputation: 373

SQL Server - CASE on where clause

I'm working on a stored procedure that is a select query. Under the WHERE clause I am filtering the return results to return only if there is a PageTitle= 'STA'

Here is am example of my query :

@InputCountryId INT, 
@InputIndustryId INT


AS 
BEGIN

SELECT 
      r.IdCountry,
      r.IdIndustry,
      r.PageTitle, 
      r.IndustryName

From dbo.Reports r

WHERE 
     r.IdCountry = @InputCountryId
     r.IdIndustry = @InputIndustryId
     r.PageTitle = 'STA'
END

The ending condition r.PageTitle I would like it to be applied ONLY IF InputCountry = 1 if not; do not include the filter.

I've attempted this by including a CASE. I am having a syntax error any time I try and introduce this case. Is this something that is possible? Am I implementing it incorrectly?

Here is an example of the stored proc with the CASE included.

    @InputCountryId INT, 
    @InputIndustryId INT
    
    
    AS 
    BEGIN
    
    SELECT 
          r.IdCountry,
          r.IdIndustry,
          r.PageTitle, 
          r.IndustryName
    
    From dbo.Reports r
    
    WHERE 
         r.IdCountry = @InputCountryId
         r.IdIndustry = @InputIndustryId
CASE WHEN @InputCountryId = 1 THEN
         r.PageTitle = 'STA'
END



    END

Upvotes: 0

Views: 44

Answers (3)

Bryan
Bryan

Reputation: 155

I can't test with your data, but here is a CASE in a WHERE clause that works.

DECLARE @Variable INT = 3

SELECT GETDATE()
WHERE 1 =
  CASE
    WHEN @Variable = 1 THEN 1
    WHEN @Variable = 3 THEN 1
    WHEN @Variable = 5 THEN 1
    ELSE 0
  END

Upvotes: 0

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30545

You dont need case statement. You can use OR clause

 WHERE 
     r.IdCountry = @InputCountryId
     r.IdIndustry = @InputIndustryId
     (@InputCountryId != 1 OR r.PageTitle = 'STA')

this only filters PageTitle with STA when InputCountry is 1

Upvotes: 1

iceblade
iceblade

Reputation: 641

Try it this way:

WHERE 
     r.IdCountry = @InputCountryId and
     r.IdIndustry = @InputIndustryId and
     (@InputCountryId <> 1 or r.PageTitle = 'STA')

Upvotes: 2

Related Questions