Reporting Services - Return Null using '%'
When selecting % from one of my report parameters, null fields are not returned
An example of one of the datasets for the report parameter:
SELECT Display AS PlansAdmin
FROM LUGenInfoPlansAdmin
WHERE Display IS NOT NULL
UNION
SELECT '%' AS PlansAdmin
ORDER BY PlansAdmin
The WHERE clause of my result set
WHERE (Projects.WMPlansAdmin LIKE @PlansAdmin)
My question is, can I dynamically change the where clause to include NULLS
(Projects.WMPlansAdmin LIKE @PlansAdmin OR Projects.WMPlansAdmin IS NULL)
when users select the '%' parameter?
I've tried setting the where clause as a variable, but this gives me a syntax error:
DECLARE @WhereClause VARCHAR(100)
IF (@Display = '%')
SET @WhereClause = '@Projects.WMPlansAdmin LIKE ' + @Display + ' OR Projects.WMPlansAdmin IS NULL'
ELSE
SET @WhereClause = '@Projects.WMPlansAdmin LIKE ' + @Display
SELECT ...
FROM ...
WHERE @WhereClause
..any suggestions?
|