MSSQL 2008: Problems with the 'Case' - statement

Question

I'm having some troubles finding a solution to my SQL-Problem. I've tried google but so far my search didn't give me any statisfactory results.

I have an SSRS report with two parameters:

 @SupplierId NVARCHAR (May contain NULL)
 @EmployeeId NVARCHAR (May contain NULL)

My original query retrieved all the employees who came in service during the last year:

SELECT Name, Surname from dbo.Employee Where Employee.DateInService > DATEADD(year,-1,GETDATE())

Right now i want to add those parameters to the query using the following logic.

Remark this is pseudo SQL:

SELECT
...
FROM ...
WHERE Employee.DateInService > DATEADD(year,-1,GETDATE()) AND
IF (LEN(RTRIM(@SupplierId)) = 0 Or @SupplierID IS NULL ) THEN
      dbo.Employee.EmployeeId = @EmployeeId
Else
      dbo.Employee.SupplierId = @SupplierId

My search sofar led me to the Case statement. I made a query which contains an syntax error (obviously). My base query:

SELECT
...
FROM ...
WHERE Employee.DateInService > DATEADD(year,-1,GETDATE()) AND
CASE WHEN (LEN(RTRIM(@SupplierId)) = 0) THEN
      dbo.Employee.EmployeeId = @EmployeeId
Else
      dbo.Employee.SupplierId = @SupplierId

Error: Syntax error near '='.

Question 1: Why does he give an error near the '='?

Question 2: How do i correctly implement the following:

CASE WHEN (LEN(RTRIM(@SupplierId)) = 0 "Or @SupplierId is null" ) THEN

Instead of

CASE WHEN (LEN(RTRIM(@SupplierId)) = 0) Then dbo.Employee.EmployeeId = @EmployeeId
     WHEN (@SupplierId IS NULL) Then dbo.Employee.EmployeeId = @EmployeeId
     ELSE dbo.Employee.EmployeeId = @EmployeeId END

Note: if i've missed a post during my google searches, please don't hesitate to point it out.

Thanks for your help

thats SAP for you my friend (not my call). Nor will it ever be. EmployeeIds here look like '00004567', ' 00123458' ,... — User999999, Feb 07 '14 at 08:38
`CASE` is an *expression*. It produces a *value*. It's not a control flow *statement*. — Damien_The_Unbeliever, Feb 07 '14 at 08:53
@Damien_The_Unbeliever Still, the construction would work if `CASE` was allowed to result in boolean expressions. — ypercubeᵀᴹ, Feb 07 '14 at 09:31
@ypercube - We're still waiting for SQL Server to expose a boolean data type, so I guess that day's a long way off yet. — Damien_The_Unbeliever, Feb 07 '14 at 09:32

score 2 · Accepted Answer · edited May 23 '17 at 12:28

You can't change the actual query predicate like that with CASE - there are 2 distinct queries depending on the value of @SupplierId. You can conditionally apply the filter as follows (I've assumed the @SupplierId = null flow is the same as the whitespace branch:

SELECT
...
FROM ...
WHERE Employee.DateInService > DATEADD(year,-1,GETDATE()) 
  AND
  (
    (dbo.Employee.EmployeeId = @EmployeeId 
      AND (LEN(RTRIM(@SupplierId)) = 0 OR @SupplierId IS NULL))
    OR
    (dbo.Employee.SupplierId = @SupplierId AND LEN(RTRIM(@SupplierId)) > 0)
  )

Although this can be prone to query plan sniffing related performance issues, in which case you might need to consider an alternative approach, e.g. using parameterized dynamic sql to build up and execute the sql, as there are 2 distinct process flows through the query.

Edit

As per Ypercube's comment above, in order to provide the boolean result needed for the predicate, if you can find a ~~hack~~ workaround is to find a way to project a COMMON scalar from each of the CASE .. WHEN row and then do a comparison of the scalar. In the example below, projecting a yes / no flag.

SELECT * FROM dbo.Employee 
WHERE
    CASE 
        WHEN (LEN(RTRIM(@SupplierId)) = 0 OR @SupplierId IS NULL) 
          THEN CASE WHEN dbo.Employee.EmployeeId = @EmployeeId THEN 1 ELSE 0 END
          ELSE CASE WHEN dbo.Employee.SupplierId = @SupplierId THEN 1 ELSE 0 END
    END = 1;

But the big problem with this approach is performance - the above will require a full scan to determine the results.

Wil try it in an hour or so. I'll keep you posted. Thanks for the effort already made though! :) — User999999, Feb 07 '14 at 08:52

MSSQL 2008: Problems with the 'Case' - statement

1 Answers1