Using CASE with WHERE

Question

I want to use CASE with WHERE, so if countryId is -1, do not select anyone, and if you want, select the value Here is my non-working code

DECLARE @countryId int=-1 --for example SELECT * FROM dbo.bbmf WHERE status=1 AND countryId = CASE WHEN @countryId = -1 THEN --select any ELSE countryId=@countryId END 

thanks for the help

Answer 1

I like them like this:

 DECLARE @countryId int=-1 SELECT * FROM dbo.bbmf WHERE status=1 AND 1 = CASE WHEN @countryId = -1 THEN 0 WHEN countryId=@countryId THEN 1 ELSE 0 --<<probably don't need the ELSE END 

Answer 2

Your question is incomprehensible, so I see two results that you may wish to achieve.

The first depends only on the countryID . Therefore, if countryID is -1, do not select any entry:

IF you do not have a countryID that is -1, then you can use this simple choice, because if @countryID = -1 nothing will return (due to AND), and if not, 1, then the necessary records will return:

 SELECT * FROM dbo.bbmf WHERE status=1 AND ( countryId=@countryId ) 

(In addition to the first solution, if you have a country with id = -1, you should change AND ( countryId=@countryId ) To AND ( countryId=@countryId AND @countryId <> -1) )

The second is that if countryId = -1 , you only need to check the status and get each record using status = 1 , then you should use this statement:

 SELECT * FROM dbo.bbmf WHERE status=1 AND (countryId = -1 OR countryId = @countryId) 

Answer 3

Executing a SELECT regardless of whether the country identifier is -1 or not, which is a redundant call. If you want to simply run the SELECT statement, if CountryId is not -1, then just filter it in the WHERE or use Conditional :

Filtered:

 SELECT * FROM dbo.bbmf WHERE status=1 AND countryId != -1 

Using conditional:

 IF (@countryId != -1) BEGIN -- SELECT STATEMENT HERE END 

Answer 4

Try DEMO

 SELECT * FROM dbo.bbmf WHERE status=1 AND countryId = @CountryID AND @countryId <> -1 

Answer 5

 SELECT * FROM dbo.bbmf WHERE status=1 AND countryId <> -1 

Answer 6

I cannot find an example of using your code, but perhaps you can run a sentence with an impossible where clause, for example:

SELECT * FROM dbo.bbmf WHERE status = 1 AND 1> 2

But, as some say, if you do not want any result, just do not run the query.