CODEWITHSUNDEEP
sqlsql-serverreporting-servicesreporting
er3000
er3000

Reputation: 13

CASE expression working in SQL Server but not working in Visual Studio Reporting Services

There is a where statement:

WHERE (@obsolete=(case  when part.drawissno = 'OBS' then 'OBS' else 'NO' end) or @obsolete is null)
and ((CASE WHEN part.sm = 'MANUFACTURED' THEN mpfmain.mpflang END) = @country or @country IS NULL)

The parameters work correctly in SQL Server, but when added to the Report, all values, except null, don't work. Parameter @country has values: GERMANY, SPAIN, FRANCE, ITALY. The parameter in report works only when the user has to input the country himself. If write in parameter properties the available values, they do not work. Other parameters, which are not included here, work correctly, but they don't have CASE within themselves. Could it be the problem with the CASE expression?

Upvotes: 0

Views: 248

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

case expressions just make where clauses harder to follow and harder to optimize. They can usually be rewritten without case:

where (@obsolete is null or part.drawissno = @obsolete) and
      (@country is null or (part.sm = 'MANUFACTURED' and mpfmain.mpflang = @country))

Upvotes: 1

Morten
Morten

Reputation: 414

I see 2 possible problems with the second CASE expression:

1) If mpfmain.mpflang is NULL then you compare with NULL with the equal sign. This will not work even if @country is also NULL.

2) You are missing an ELSE part. It is of course not mandatory, but consider what happens if either part.sm is NULL, or part.sm has another value than 'MANUFACTURED'.

You might benefit from the ISNULL method around columns or parameters than can be NULL. You can read more about the ISNULL method here:

https://learn.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql

Upvotes: 0

Related Questions