Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Jul 2007
    Posts
    50

    Question Unanswered: Selecting multiple values from a INT Field.

    Hi,

    If I have a field which is an INT and holds different status codes, I would like to be able to seach this field for multiple values.

    IE

    Status codes =

    New = 1
    Open = 2
    Resolved = 3
    Closed = 4

    If I want to search for all items that are New or Closed, I would like to be able to provide 1 and 4 at search time.

    I was thinking about using the Binary process. ie.

    New = 1
    Open = 2
    Resolved = 4
    Closed = 8

    And be able to provide a amalgamation on the search criteral (9 in this instance) and for it to be able to work out 1 and 8 and in this critearia and if these values are in the field.

    Any ideas or am I going about it all the wrong way?

    Cheers,

    Paul.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    WHERE status IN (1,4)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2007
    Posts
    50
    Hi,

    Yeah I think I was trying to make things more difficult for myself, but on thing I am not 100% sure about is passing the param for the IN statement to the proc.

    Do I just use a string?

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    typically I pass in a string that I parse into a table variable and then I join to my table variable and that typically takes care of the filtering I am trying accomplish.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jul 2007
    Posts
    50
    I'm thinking of going with an XML param and an IF statement in the where clause.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here's another in-elegant option
    Code:
    DECLARE @values varchar(20)
        SET @values = '|1|4|'
    
    SELECT *
    FROM   my_table
    WHERE  CharIndex('|' + Convert(varchar, status) + '|', @values) > 0
    Roll on 2008's ability to pass table variables to procedures!
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Roll on 2008's ability to pass table variables to procedures!
    I'll be interested to see Pat's thoughts on this.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    my feeble little brain is having a hard time fathoming an objection to table parameters. maybe we can just keep bumoing this thread until the irishman weighs in.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    His objection to deliminted strings is to do with relational contracts. I don't know if he will consider table parameters non-relational, or if he now considers the contract to have changed.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jul 2007
    Posts
    50
    Bearing in mind my stored proc will have around 10 fields that can me selected using multiple values ala a IN clause, what is going to be the easiest way to do this?

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quickest and dirtiest is George's idea.
    Most thorough and elegant is XML.
    Best compromise is Sean's idea.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jul 2007
    Posts
    50
    With the XML, I can do a join on the XML input, but these params are optional, so if it's NULL how do I say select all?

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flump
    Quickest and dirtiest is George's idea.
    Score! +1 point
    George
    Home | Blog

  14. #14
    Join Date
    Jul 2007
    Posts
    50
    Not always the best method, but we are talking DB's here..

    This works.

    AND IM_CurrentPriority IN (SELECT ParamValues.ID.value('.','VARCHAR(20)')FROM @Priority.nodes('/priority/privalue') as ParamValues(ID))

    But attempting to cater for a NULL value doesn't
    AND (@Priority IS NULL or (IM_CurrentPriority IN (SELECT ParamValues.ID.value('.','VARCHAR(20)')FROM @Priority.nodes('/priority/privalue') as ParamValues(ID)))
    Last edited by MrRalphMan; 10-28-08 at 11:44.

  15. #15
    Join Date
    Apr 2007
    Posts
    183
    Do the binary calculation on client side and pass value to stored procedure?

    Code:
    -- Prepare sample data
    DECLARE	@Sample TABLE
    	(
    		data VARCHAR(20),
    		binVal INT
    	)
    
    INSERT	@Sample
    SELECT	'Peso', 15 UNION ALL
    SELECT	'dbForums', 9 UNION ALL
    SELECT	'SQL Server', 5 UNION ALL
    SELECT	'Microsoft', 3
    
    -- Prepare user supplied binary value
    DECLARE	@param INT
    
    -- Calculate the binary value for bit 1 and 4 set at client side. Value is 9.
    SET	@param = POWER(2, 1 - 1) + POWER(2, 4 - 1)
    
    -- Search the table for exact match
    SELECT	data
    FROM	@Sample
    WHERE	binVal & @param = @param
    
    -- Search the table for match on any of the given bits
    SELECT	data
    FROM	@Sample
    WHERE	binVal & @param > 0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •