CODEWITHSUNDEEP
sqlreporting-servicessql-server-2008-r2syntax-errorcase
User999999
User999999

Reputation: 2520

MSSQL 2008: Problems with the 'Case' - statement

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

Upvotes: 1

Views: 154

Answers (1)

StuartLC
StuartLC

Reputation: 107247

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.

Upvotes: 2

Related Questions