Search code examples
sqlsql-serverreporting-servicescascadingdropdown

Does my SQL query used to populate a drop-down make sense?

I'm relatively new to SQL - I'm not sure if I'm doing this right. I added pretty pics. I have a query where I want to populate a parameter(based on another parameter that you input via a drop-down menu, which is the @ResponseRange var you see)

SELECT  
CASE
 WHEN crr.Name = 'Prescreens'  THEN  null
ELSE crr.Codes 
END
FROM      Client_Response_Ranges_for_SSRS_Respondent_Answer crr
where  crr.Codes  = @ResponseRange;

This is how that crr tables looks(it was just custom table for query):

enter image description here enter image description here

ResponseRange enter image description here

More info: so I have 6 parameters, and they are

BeginDate

EndDAte

SurveyID

SupplierID

ResponseRange

ClientResponseRange

The thing is that ClientResponseRange depends on ResponseRange - so actually ClientResponseRange is hidden. For all the choices of ResponseRange except one, I will automatically use whatever ResponseRange's value for ClientResponseRange. I'm doing something wrong, but I don't know what.. thanks very much any tips appreciated.

Solution

  • That is because your query is trying to check for NULL = NULL

    Add this to your query, if that is what you want to do:

    SET ANSI_NULLS OFF

    However, there are some ambiguties with your query:

    1. Why is the Value of Codes for "Prescreens" showing as NULL in the table, by "3" in the property page?
    2. If you are trying to populate @ResponseRange with the query output, then where is the parameter declared?
    3. Also, the approach to populate this variable is incorrect.
    4. If the variable is pre-populated, could you also tell us how it is populated?

    Raj