In SQL Server, how do I select NULL for two specific cases (query included)

Question

I have this query:

SELECT crr.Codes
FROM Client_Response_Ranges_for_SSRS_Respondent_Answer crr
WHERE crr.Name =  @ReportParameter1

but I want to say that - if @ReportParameter1 (which is a parameter I'm using in a SSRS report) is either All or Prescreens, then override what it usually returns (the nvarchar NULL) and return a true null instead (i.e don't return anything? ) ..

I tried something like this:

SELECT     
   CASE 
      WHEN @ReportParameter1 = 'All' THEN 'NULL'
      WHEN @ReportParameter1 = 'Prescreens' THEN NULL
      ELSE crr.Codes 
   END

but it does not work and gives me an error that says:

An error occurred during local report processing.
An error has occurred during report processing.
Query execution failed for dataset 'DataSet2'.
The variable name '@ReportParameter1' has already been declared. Variable names must be unique within a query batch or stored procedure.

In SQL there is a big difference between not returning anything (no rows) and returning a null value for a field. Which would you like? — Jamie F
Also, 'null' is a char/varchar but null (no quotes) is a null — gmlobdell

ajp ajp · Accepted Answer · 2013-01-02T03:42:30

I removed a single quote before null and giving a alsias name for the select column. Try below:

SELECT     CASE when @ReportParameter1 = 'All' then null 
                when @ReportParameter1 = 'Prescreens' then null
                else crr.Codes end as codes
FROM         Client_Response_Ranges_for_SSRS_Respondent_Answer crr
where crr.Name =  @ReportParameter1

In SQL Server, how do I select NULL for two specific cases (query included)

1 Answers