CODEWITHSUNDEEP
sql-serversql-server-2008reporting-servicesssrs-2008
Caffeinated
Caffeinated

Reputation: 12484

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

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.

Upvotes: 0

Views: 405

Answers (1)

ajp
ajp

Reputation: 1490

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

Upvotes: 2

Related Questions