parameter with multiple values 2

[bbeeharry]

Hi,

i have a parameter which can take multiple values.
how can i reprent this in my sql?

Thks,
bbe

 

[SgtJarrow]

I had a similar question a while back and stumbled on an answer that has worked good for me. The first part is a Split function for SQL. You pass this function a delimited string and it will populate part of your IN statement.

My split function is this:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[Split]
(
	@ItemList NVARCHAR(4000), 
	@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))  
AS      

BEGIN    
	DECLARE @tempItemList NVARCHAR(4000)
	SET @tempItemList = @ItemList

	DECLARE @i INT    
	DECLARE @Item NVARCHAR(4000)

	SET @tempItemList = REPLACE (@tempItemList, ' ', '')
	SET @i = CHARINDEX(@delimiter, @tempItemList)

	WHILE (LEN(@tempItemList) > 0)
	BEGIN
		IF @i = 0
			SET @Item = @tempItemList
		ELSE
			SET @Item = LEFT(@tempItemList, @i - 1)
		INSERT INTO @IDTable(Item) VALUES(@Item)
		IF @i = 0
			SET @tempItemList = ''
		ELSE
			SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
		SET @i = CHARINDEX(@delimiter, @tempItemList)
	END 
	RETURN
END

And then when i want to use it, I do something like:

AND e.RecKey IN (SELECT Item FROM dbo.Split(@Routes, ','))

I know I picked this up from a e-mag or forum somewhere, but I don't remember exactly where. I think it was part of an SQLServer article if you want to dig for it.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[AlexCuse]

Something like this would normally be better, performance wise, than using IN:

select * from myTable a
inner join
(
select * from dbo.Split(@params, ',')
) b
on a.Col = b.Col

Hope it helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

See the FAQ in this forum, "Passing a list of parameters to a stored procedure," or something like that, parts 1, 2 and 3.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[vbkris]

[qoute AlexCuse]
Something like this would normally be better, performance wise, than using IN:
[/qoute]

hey alex,

from the testing that i carried out (basically comparing the two query plans) i found that if the columns are indexed then a join is better. otherwise both an inner join and an IN seem to give me the same performance.

inviting your comments on this...

Known is handfull, Unknown is worldfull

 

[AlexCuse]

Hey vbKris, what did you do for testing?

I suppose that it would depend on the size of your result sets being joined, but I think that with larger parameter lists and/or tables that the join would start to pull ahead in terms of performance (regardless of indexes), due to the number of comparisons that need to be performed, but I'm not exactly sure.

I should also add that every split function that I've used places a primary key constraint on the table variable that is returned. Unless I am mistaken, using IN you would be giving up performance advantages that setting this primary key gives you.

To the OP - one small change I would make to the code posted by MstrMage:

RETURNS @IDTable TABLE (Item VARCHAR(50) PRIMARY KEY CLUSTERED)

This this will prevent duplicate values from finding their way into your list and skewing query results (not a problem using IN method, but they could slow things down), and also give you a substantial performance boost using the join method.

A quick googling turned up this:

http://www.ftponline.com/vsm/2004_07/magazine/columns/databasedesign/

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[SgtJarrow]

Once again, Alex has shown his knowledge in the SQL arena greatly exceeds my own. Thanks for the tip on the clustering for the return table. That should help my situations. And also thanks for the suggestion of JOINS vs IN statements. Most of the sets I work with are small, but sometimes any little performance improvement can be a great help.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[TJRTech]

bbeeharry asks: "i have a parameter which can take multiple values. how can i reprent this in my sql?"

Okay, others seem to be answering this question as if bbeeharry is asking about passing multiple values at a time in a single parameter.

But what if he is asking about a parameter which is restricted and can be set to a known set of values...sort of like a user-defined type?

That's how I read the question. If the known list is small, then you can use a constraint to check that the parameter has one of the restricted values from a static list.

TJR

 

[vbkris]

>>RETURNS @IDTable TABLE (Item VARCHAR(50) PRIMARY KEY CLUSTERED)

neat! i was always under the impression that we cannot create indexes on Table variables. tweaking the Primary Key constraint was a great idea!!!

Known is handfull, Unknown is worldfull

 

[AlexCuse]

TJRTech - I obviously didn't read it that way. A parameter that can take multiple values <> a parameter whose value can only be one of a set list, IMO. If it was to be in a set list of values, I think it would be preferable to address this issue in client-side code though?

I suppose you could be right, but as bbeeharry seems to be MIA at the moment, we might never know :-(

vbKris - I had that impression for a long time too, I believe it was gmmastros who enlightened me . You can't really create indexes, but you can give table variables a primary key (which in most cases is sufficient).

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom