none
IS NULL/IS NOT NULL IN SSRS

    问题

  • Hi,

    I have report that have to pass a condition IS NULL OR IS NOT NULL in SSRS 2008. I don't want to use the Expression based syntax (I found already lot of problems to generate a long query)

    Simply in query I want to handle

    Select ColA from TableA where ColA IS Null

    or

    Select ColA from TableA where ColA IS NOT Null

    TIA,

    Holy

    2012年3月23日 17:11

答案

  • Hi Holy - Your question is a bit ambiguous. If you are simply wanting to return all rows where a column IS NULL or the column IS NOT NULL, you could use:

    Select ColA from TableA where ColA IS Null or IS NOT Null

    But since this covers all possible values for that column, it would be the same thing as:

    Select ColA from TableA

    I don't think this is your real intent, I'll assume you want to pass the condition via a report parameter named "IsNull", and the possible values are "IS NULL", or "IS NOT NULL". If so, there are a couple ways you could do this. Keep in mind that a dataset query is commonly a SELECT statement, but it can actually be any valid TSQL batch as long as the batch returns one and only one result set.

    The first would be to build a string that includes the text from your parameter, and then use a dynamic Execute (string) statement to run it. For example, if your parameter is named "IsNull", your dataset query could look like:

    exec('Select ColA from TableA where ColA ' + @IsNull)

    The second way would be to use a conditional IF statment to run one of two static queries based on an IF statement:

    if @IsNull = 'IS NULL'
     select ColA from TableA where ColA IS NULL
    else
     select ColA from TableA where ColA IS NOT NULL

    Both of these techniques have their risks and pitfalls; you have to be careful about TSQL injection whenever you use EXECUTE(string), in this limited case it would be safe, but I assume you simplified it for the sake of clarity in the question. The second approach would make it harder to maintain two identical copies of complex queries that only differ by the WHERE clause.

    I would instead suggest that you try not to depend on using TSQL tokens or null values in your parameters, and use literal strings instead. For example, you could use "VALUE NOT SET" or "VALUE SET" as the available values, and then use them in your query as:

    select ColA from TableA where
    (@IsNull = 'VALUE SET' and ColA IS NOT NULL)
    or
    (@IsNull = 'VALUE NOT SET' and ColA IS NULL)

    I have a blog posting here that addresses some of these issues and suggestions that may be useful to you.

    I hope this helps, Dean

    • 已标记为答案 Holysmoke 2012年3月26日 7:41
    2012年3月24日 0:20
  • Thanks Dean,

    I raised this question on this forum because I would like to avoid SP/Expression based syntax from maintainence point of view.

    Even though is not a perfect solution because of the lack of readablity of SQL and I am forced to agree with this solution (I would say a workaround rather ;))

    select ColA from TableA where
    (@IsNull = 'VALUE SET' and ColA IS NOT NULL)
    or
    (@IsNull = 'VALUE NOT SET' and ColA IS NULL)

    Many thanks,

    Holy

    • 已标记为答案 Holysmoke 2012年3月26日 8:14
    2012年3月26日 8:13

全部回复

  • You can do that with simple query/stored procedure associated with the dataset. Please clarify the question.

    Regards,


    Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.

    2012年3月23日 18:04
  • Hi Holy - Your question is a bit ambiguous. If you are simply wanting to return all rows where a column IS NULL or the column IS NOT NULL, you could use:

    Select ColA from TableA where ColA IS Null or IS NOT Null

    But since this covers all possible values for that column, it would be the same thing as:

    Select ColA from TableA

    I don't think this is your real intent, I'll assume you want to pass the condition via a report parameter named "IsNull", and the possible values are "IS NULL", or "IS NOT NULL". If so, there are a couple ways you could do this. Keep in mind that a dataset query is commonly a SELECT statement, but it can actually be any valid TSQL batch as long as the batch returns one and only one result set.

    The first would be to build a string that includes the text from your parameter, and then use a dynamic Execute (string) statement to run it. For example, if your parameter is named "IsNull", your dataset query could look like:

    exec('Select ColA from TableA where ColA ' + @IsNull)

    The second way would be to use a conditional IF statment to run one of two static queries based on an IF statement:

    if @IsNull = 'IS NULL'
     select ColA from TableA where ColA IS NULL
    else
     select ColA from TableA where ColA IS NOT NULL

    Both of these techniques have their risks and pitfalls; you have to be careful about TSQL injection whenever you use EXECUTE(string), in this limited case it would be safe, but I assume you simplified it for the sake of clarity in the question. The second approach would make it harder to maintain two identical copies of complex queries that only differ by the WHERE clause.

    I would instead suggest that you try not to depend on using TSQL tokens or null values in your parameters, and use literal strings instead. For example, you could use "VALUE NOT SET" or "VALUE SET" as the available values, and then use them in your query as:

    select ColA from TableA where
    (@IsNull = 'VALUE SET' and ColA IS NOT NULL)
    or
    (@IsNull = 'VALUE NOT SET' and ColA IS NULL)

    I have a blog posting here that addresses some of these issues and suggestions that may be useful to you.

    I hope this helps, Dean

    • 已标记为答案 Holysmoke 2012年3月26日 7:41
    2012年3月24日 0:20
  • hi Holysmoke,

    from your question what I can understand is you want to pass an optional parameter from report null or not null

    correct me if i am wrong you can do it in query simply in where cluase i-e

    @paramter nvarchar(20)  ----paramter defined in stored procedure or in simple query text

    Select ColA from TableA where  (ColA = @parameter or @parameter is null) 

    hope it will help you.


    ------------------ Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Maqbool.

    2012年3月24日 9:03
  • Thanks Dean,

    I raised this question on this forum because I would like to avoid SP/Expression based syntax from maintainence point of view.

    Even though is not a perfect solution because of the lack of readablity of SQL and I am forced to agree with this solution (I would say a workaround rather ;))

    select ColA from TableA where
    (@IsNull = 'VALUE SET' and ColA IS NOT NULL)
    or
    (@IsNull = 'VALUE NOT SET' and ColA IS NULL)

    Many thanks,

    Holy

    • 已标记为答案 Holysmoke 2012年3月26日 8:14
    2012年3月26日 8:13