Selecting multiple values from a INT Field.

MrRalphMan · #1 (**permalink**) 10-27-08, 07:54

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.

r937 · #2 (**permalink**) 10-27-08, 09:10

WHERE status IN (1,4)

MrRalphMan · #3 (**permalink**) 10-28-08, 08:33

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?

Thrasymachus · #4 (**permalink**) 10-28-08, 08:48

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.

MrRalphMan · #5 (**permalink**) 10-28-08, 08:57

I'm thinking of going with an XML param and an IF statement in the where clause.

gvee · #6 (**permalink**) 10-28-08, 09:16

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!

pootle flump · #7 (**permalink**) 10-28-08, 09:20

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.

Thrasymachus · #8 (**permalink**) 10-28-08, 09:34

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.

pootle flump · #9 (**permalink**) 10-28-08, 09:38

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.

MrRalphMan · #10 (**permalink**) 10-28-08, 09:55

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?

pootle flump · #11 (**permalink**) 10-28-08, 10:04

Quickest and dirtiest is George's idea.
Most thorough and elegant is XML.
Best compromise is Sean's idea.

MrRalphMan · #12 (**permalink**) 10-28-08, 10:14

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?

gvee · #13 (**permalink**) 10-28-08, 10:17

Quote:

Originally Posted by pootle flump

Quickest and dirtiest is George's idea.

Score! +1 point

MrRalphMan · #14 (**permalink**) 10-28-08, 10:19

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)))

Peso · #15 (**permalink**) 10-28-08, 11:37

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

Internet Services

Web Development

Digital Marketing

Consumer Tech