Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Is single value any of the multi values?

Beantwortet Is single value any of the multi values?

  • Tuesday, February 19, 2013 10:30 PM
     
     
    Sign In to Vote
    0

    I have multi-value parameter of integers.

    I want to know if "1" is ANY of the values, does not matter the position.

    The following does not work:

    =Parameters!ids.Value = 1

    ... and of course using Value(1) works, but that's dependent on the 1 being in that position.  I need if 1 appears as ANY value in the list.

    Thanks!

    edit:  If the value isn't in the list, I want to hide a textbox, using a visibility expression.
    • Edited by Mini Button Tuesday, February 19, 2013 10:44 PM
    •  
     

All Replies

  • Tuesday, February 19, 2013 11:39 PM
     
     Answered
    Sign In to Vote
    0

    There are several ways to do this.  For example you may want to use JOIN to concantenate all of the values and then checking to see if 1 is has also been selected (http://technet.microsoft.com/en-us/library/dd207127(v=SQL.100).aspx).

    However, another easy way may be to create another Dataset and parameter such that you show/hide the textbox based upon the parameter value.

    For the other Dataset (let's name it DSHide as an example), you can have something like this:

    SELECT CASE
    WHEN 1 IN (2) THEN 'TRUE'
    ELSE 'FALSE'
    END AS 'HIDE'

    Of course, instead of "2", you can pass the parameters such as @myParameter which may or may not contain the value "1".

    Create another parameter (let's name it PHide as an example) default to the value of this DSHide.

    Then for the visibility property of your textbox, set "Show or hide based on an expression" to:

    =CBool(Parameters!PHide.Value)

    Of course, you may interchange the 'TRUE' or 'FALSE' value as needed in the DSHide in order to hide/show accordingly to your needs.

    Note that the above example is if you care as if the user has selected "1" as one of the values in the parameters.  In other words, the textbox will toggle its visibility depending on the user's input.

    If you want to toggle the textbox regardless of whether or not the user has selected "1" as the values in the parameters (in other words, even if the user does not select "1", toggle it according to the dataset), you may want to change DSHide to a subquery using the other dataset instead of using @myParameter.

    Hope this helps! 

    • Marked As Answer by Mini Button Wednesday, February 20, 2013 12:02 AM
    •  
     
  • Wednesday, February 20, 2013 12:00 AM
     
     
    Sign In to Vote
    0

    The issue with JOIN is that "1" will be found in "11" and "12" etc...

    edit: Nevermind, fixed by forcing a type in 3rd parameter.  Thanks.

    • Edited by Mini Button Wednesday, February 20, 2013 12:02 AM
    •  
     
  • Monday, February 25, 2013 10:07 PM
     
     
    Sign In to Vote
    0

    I was wrong, the JOIN is indeed seeing 1 as being in the list if 11, 12, 13, etc. are in the list.  Is there a way to make it notice comma separated values as distinct entities?

    Edit:  Nevermind, figured a method myself.  Put commas at start and end manually, and searched for each number with surrounding commas using INSTR.  Thanks.

    • Edited by Mini Button Monday, February 25, 2013 10:25 PM
    •