Selecting multiple values from a INT Field. - Page 2

Peso · #16 (**permalink**) 10-28-08, 11:50

Quote:

Originally Posted by pootle flump

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

1) Let us hope parameters are supplied in same order as concatenated string
2) Let us hope the concatenated string has no more values such as "|1|2|4|"

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

Quote:

Originally Posted by Peso

1) Let us hope parameters are supplied in same order as concatenated string
2) Let us hope the concatenated string has no more values such as "|1|2|4|"

Peter, I don't follow what you're saying here...

Thrasymachus · #18 (**permalink**) 10-28-08, 12:47

Quote:

Originally Posted by Peso

1) Let us hope parameters are supplied in same order as concatenated string
2) Let us hope the concatenated string has no more values such as "|1|2|4|"

on number 1, I think he misunderstands. Mine simply solves the problem of a variable number of parameters that fit into a IN (1,2,3) scenario.

If the concatenated string pertains to various parameters matching different fields, yeah my approach is stupid.

gvee · #19 (**permalink**) 10-28-08, 12:53

I'm still lost

MrRalphMan · #20 (**permalink**) 10-29-08, 04:05

Quote:

Originally Posted by Peso

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

Dude, I like this, I like this a lot.

What is the & operation between the two INT's to have this work?

Paul.

Peso · #21 (**permalink**) 10-29-08, 05:16

It is a binary AND in T-SQL.

MrRalphMan · #22 (**permalink**) 11-01-08, 04:08

Cheers, still don't quite understand it, but it works so I'm happy.

Thanks.

MrRalphMan · #23 (**permalink**) 03-14-10, 06:08

Sorry for bumping a rather old thread, but this doesn't seem to work well for large number of boolean values...

Ie This works fine when I have a low number, but once we have a larger number (30ish), the int values grow to silly sizes and the join to the table holding the string values impacts performance in a silly way.

With the join to the large boolean int value a full query takes about 90 seconds, with it joined to the table key fields, int starting at 1 and increasing by 1, it takes 20 seconds.

I have gotten around this by passing the seperate values searched for as an XML string, but would like to know the reason behind this performance hit?

Cheers,

Paul.

Internet Services

Web Development

Digital Marketing

Consumer Tech