WHERE IN @MyCSV - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

WHERE IN @MyCSV

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-30 : 14:10:04

DECLARE @Mystring varchar (100)

SET @Mystring = '103, 104, 105'

SELECT Coursename
FROM Courses
WHERE CourseID in @Mystring

I need to get a query similar to the above working in a stored procedure. Is there a way to specify a CSV string passed as a parameter without using dynamic SQL?

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-30 : 14:26:21

In this thread I wrote some code

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=24172

that parses an input variable which would be comma delimited...it actually builds a SQL SELECT statement, but you could just as easily INSERT the delimeted values in to a temp table and join to it....

There's a sample in this thread:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=25746

Let me know if this doesn't make sense.

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-30 : 14:55:24

Hi Brett,

Your solutions involve dynamic SQL and didn't address the IN clause (at least if they did I missed it).

Rob's article

[url]http://www.sqlteam.com/item.asp?ItemID=2652[/url]

might resolve the problem (but it seems like a lot of work

DECLARE @Mystring varchar (100)

SET @Mystring = '103, 104, 105'

SELECT Coursename
FROM Courses
WHERE CourseID in (Include Rob's SELECT here on @Mystring )

Rob's SELECT is no small effort, but it may be the only method. The IN operator seems to require a column of values (as opposed to a CSV string.)

Is there a less painful way like passing an array or other datatype to the stored procedure?

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-30 : 14:59:42

I feel the best way to do this (and this has been mentioned at this site many times -- I don't take credit for this idea!) is to create a UDF that accepts a CSV as an argument and returns a table.

Then, either join to that resulting table or use the IN operator.

My version of that function looks like this:

edit: editted to fix a minor bug.

CREATE function CSVTable(@Str varchar(7000))
returns @t table (numberval int, stringval varchar(100), DateVal datetime)
as
begin

declare @i int;
declare @c varchar(100);

set @Str = @Str + ','
set @i = 1;
set @c = '';

while @i <= len(@Str)
begin
if substring(@Str,@i,1) = ','
begin
insert into @t
values (CASE WHEN isnumeric(@c)=1 THEN @c else Null END,
rtrim(ltrim(@c)),
CASE WHEN isdate(@c)=1 then @c else Null END)
set @c = ''
end
else
set @c = @c + substring(@Str,@i,1)
set @i = @i +1
end
return
end

Note that the above works for all datatypes -- dates or text or numbers (ints only, but that could be fixed as well).

So, for example:

Select * from
People
WHERE People.Name in (SELECT StringVal FROM dbo.CSVTable('Jeff,Bill,Pete,Eddy, John,Mike'))

Select * from
Numbers
WHERE Numbers.Number in (select numberval from dbo.CSVTable('1,2,34,5,5,6,7,8'))

Select * from
Dates
WHERE Dates.Date in (Select dateval from dbo.CSVTable ('1/1/2000','1/2/2000','1/3/2000'))

Of course, the function works with @variables or fieldnames or any other expression:

select * from dbo.CSVTable(@ListOfNames)

- Jeff

Edited by - jsmith8858 on 04/30/2003 15:01:15

Edited by - jsmith8858 on 05/24/2003 14:33:25

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-30 : 15:13:57

Thanks Mr. Cross Join,

The UDF is a great solution

I suppose the WHILE loop is OK as there will only be 1, 2 or 3 items in the CSV list. Could omit the WHILE and use a Tally table like Rob did in the earlier mentioned article. I doubt it would be faster for small sets.

Sam

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2003-04-30 : 15:18:38

Also worth a read (it's bloody long though

)
[url]http://www.algonet.se/~sommar/arrays-in-sql.html[/url]

HTH
Jasper Smith

0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-30 : 15:33:51

I didn't say it didn't need alteration:

Try this:



CREATE TABLE bk_temp2 (col1 varchar(255))
GO

INSERT INTO bk_temp2 (col1) SELECT 'Apples' UNION ALL SELECT 'Peaches' UNION ALL SELECT 'Pumpkin Pie' UNION ALL SELECT 'Name'
GO

CREATE PROC usp_PROC1 @ReqColsDelimited varchar(4000) 
AS 
Declare @strSQL varchar(4000),@y int 

CREATE TABLE #bk_temp (col1 varchar(255))

SELECT @y = CHARINDEX(',',@ReqColsDelimited,1)-1 
INSERT INTO #bk_temp (col1)SELECT Substring(@ReqColsDelimited,1,@y) 

Select @ReqColsDelimited = Substring(@ReqColsDelimited,@y+2,Len(@ReqColsDelimited)-(@y+1)) 

While @y > 0 
	BEGIN 
		Select @y = CHARINDEX(',',@ReqColsDelimited,1)-1 

		If @y > 0 
			BEGIN 
				INSERT INTO #bk_temp (col1)SELECT Substring(@ReqColsDelimited,1,@y) 
				Select @ReqColsDelimited = Substring(@ReqColsDelimited,@y+2,Len(@ReqColsDelimited)-(@y+1)) 
			END 
	END 

SELECT * FROM bk_Temp2 l INNER JOIN #bk_Temp r ON l.col1 = r.col1 

DROP TABLE #bk_temp
GO 

Execute usp_PROC1 'name,type' 
GO

DROP TABLE bk_Temp2
Go

Drop Proc usp_PROC1 
GO

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-30 : 15:53:47

Brett -- I think if can't use UDF's your approach would definitely work. but the UDF solution is very generic and very fast -- you can use it over and over for any kind of table you want, and it is very flexible -- you just include it as part of the WHERE in *ANY* select statement and you're done.

It's a great UDF (not mine specifically, but that *type* of UDF) to put in your toolbox database and use as needed. It saves a lot of work and makes your code VERY readable.

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-30 : 16:11:20

Brett,

What's better about your second solution

(I was planning on going with the first UDF you posted )

Sam

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-04-30 : 16:48:44

And why not use dynamic SQL?
If you want to get Coursename out, you could use sp_executesql.

Sarah Berger MCSD

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-30 : 16:54:11

Use dynamic SQL as a last resort if there isn't a better, more direct, more optimizable & compilable approach.

In this case, there definitely is!

- Jeff

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2003-04-30 : 17:04:16

quote:
And why not use dynamic SQL?

If you read the article you'll see why

[url]http://www.algonet.se/~sommar/arrays-in-sql.html#dynamic-sql[/url]

HTH
Jasper Smith

0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-30 : 17:08:12

Great link, Jasper. Very good explaination.

You've made the Smith family name quite proud.

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-30 : 19:04:58

Jeff,

The function you provided above is written as if SUBSTRING expects the index to begin at zero (0). Is there an option in SQL 2000 for ZERO relative indexing?

If not, then two statements need to change

SET @i = 1 -- Start the index at 1 not 0

and

while @i <= len(@Str) -- Last CSV token is lost without this mod

Sam

Edited by - SamC on 04/30/2003 19:16:25

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-30 : 21:43:29

Thanks, Sam! I just kinda threw it together pretty quick and didn't thoroughly test it.

- Jeff

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-01 : 07:45:25

Sam, if you read the comments on Rob's article you will find an adaptation that I made, that you could work into a udf-type solution as an alternative to the loop Jeff suggested.

I would test both udfs though for speed. I've had mixed results.

Plus, my solution would give you a chance to use your new Tally table

.

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-01 : 08:40:00

I'm not sure I'd go with a tally table in this type of solution -- no point in adding I/O to a UDF if it doesn't need it.

Looping through a variable stored in memory vs. looping through rows in a table should not be confused -- there is a HUGE difference. the second should be avoided at all costs.

The first is pretty much a thing that computers are designed for and do quite well!

Remember, solutions using a tally table require that for EACH WHERE clause check the computer must loop through the variable in memory (remember, every usuage of a CHARINDEX() funciton or LIKE clauses must check 1 character at a time) -- for *every* row in your tally table!

As opposed to doing it just once.

Many ways to skin a cat! use the method that works for you. but always try to the use the simpliest, most direct, most efficient, and most straightforward method. whatever that may be in this case!

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-01 : 10:25:36

GREAT thread guys...

But I need some help...I'm looking at the UDF, but I'm at a loss...

In my code I pass in a csv string and the query can check for existance.

Is the UDF doing the same thing?

How is:

quote:

Select * from
People
WHERE People.Name in (SELECT StringVal FROM dbo.CSVTable('Jeff,Bill,Pete,Eddy,John,Mike'))

Different from

Select * from
People
WHERE People.Name in ('Jeff,Bill,Pete,Eddy,John,Mike')

I'm confused...

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-01 : 11:14:55

Good question ... HUGE difference.

IN ('val1, val2, val3')

is VERY different from

IN ('val1','val2','val3')

note where the quotes are. In the first, SQL sees this:

IN (SomeValue)

in the second, it sees:

IN (SomeValue1, SomeValue2, SomeValue3)

Does that make sense? the first doesn't do what you intended -- it compares to a single string, which just happens to be seperated by commas. in the second, it compares to multiple values to see if 1 matches.

And, of course, saying

IN (select something FROm anyway)

results in a LIST of values, not one big value. What you listed is closer to:

IN (select @val = @val + ', ' + something from anyway)

if that syntax even worked, which I imagine probably doesn't. it is returning 1 long string value -- not a list of values with the IN operator should compare.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-01 : 11:38:47

Thanks Jeff,

Appreciate the reply....but I'm still lost

I have take some more time and look at it closer and/or play with it.

Thanks again.

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-01 : 11:49:30

Brett:

try this, see if it helps you understand.

declare @t table (v varchar(100))
declare @t2 table (v varchar(100))
set nocount on
insert into @t
select 'jeff' union
select 'brett' union
select 'valter' union
select 'jay' union
select 'arnold'

insert into @t2
select 'jeff' union
select 'brett' union
select 'valter'

set nocount off

select * from @t -- DOES NOT WORK!
where v in ('jeff,brett,valter')

select * from @t -- WORKS FINE
where v in ('jeff','brett','valter')

select * from @t -- WORKS FINE
where v in (select * from @t2)

- Jeff

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources