CODEWITHSUNDEEP
sqlsql-server
Niranjan Singh
Niranjan Singh

Reputation: 18290

How to use Conditional statement in Case When statement?

I am trying to translate my query similar to below C# statement:

var result = Data.Where(r=> r.Year = 2017 
       && ( unit ==00000000-0000-0000-0000-000000000000" ? true: unit == r.UnitId);

Below is then query I am trying to put into table values function but get error on = operator in case statement.

SELECT * From ReportData
WHERE       
  Year = @year 
  AND
  (CASE @unit
      WHEN '00000000-0000-0000-0000-000000000000' THEN 1
    ELSE  @unit = UnitId
    END
  )

Is is possible to put condition in then block of case statement in sql? Is there any way to implement this in SQL?

Upvotes: 0

Views: 64

Answers (3)

HoneyBadger
HoneyBadger

Reputation: 15140

I think you are looking for this:

SELECT * 
FROM   ReportData
WHERE  Year = @year 
  AND  (UnitId = @unit OR @unit IS NULL)

CASE in SQL doesn't work like if in C#; it just returns an atomic value.

Upvotes: 1

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY THIS Your own query will work by making simple changes as below:

SELECT * From ReportData
WHERE       
  Year = @year 
  AND UnitId = 
  (CASE @unit
      WHEN '00000000-0000-0000-0000-000000000000' THEN 1
    ELSE  @unit
    END
  )

Upvotes: 1

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Try this way

SELECT * From ReportData
WHERE       
  Year = @year 
  AND
  (CASE @unit
      WHEN '00000000-0000-0000-0000-000000000000' THEN 1
    ELSE  @unit 
    END
  )= UnitId

Upvotes: 0

Related Questions