Passing comma delimited expressions

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)

Passing comma delimited expressions

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-10 : 07:27:02

Brian writes "I need to pass a comma delimited list of integers to a stored procedure for use in an IN clause such as the following

SELECT DISTINCT Cycle
FROM Billing
WHERE CustID IN (1, 2, 3)

with parameter ...

SELECT DISTINCT Cycle
FROM Billing
WHERE CustID IN (@CustIDs)

I've tried CASTing the CustID to varchar and sending the parameter values as "'1', '2', '3'" but that doesn't work either. I have found articles on your site that parse the list by looping through it and assembling it in some other manner or putting the values in a temp table, but for such a simple statment it seem quite a bit of trouble.

Any help would be greatly appreciated. By the way, your site has been extremely helpful for other issues.

Thanks,

Brian"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-07-10 : 07:33:25

what about

where
charindex('''' + convert(varchar,custid) + '''',@custids) > 0

could be error prone, depending on your data

Jay White
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-10 : 10:59:42

This is a common topic...

This thread should give you everything you need.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-10 : 13:49:06

I was trying to guide you towards Jesff Post in that thread, but then I tried it and I must have fubared it all up. Anyway, this is a theft of Jeff's (which Jeff mention was derived elsewhere). Jeff's udf spins through every char in the list. Mine locates the comma and goes directly there. In any event it's a GREAT concept:



USE Northwind
GO



CREATE FUNCTION udf_CSVTable( @Str varchar(7000) ) 
	RETURNS @t TABLE (numberval int, stringval varchar(100), DateVal datetime) 
AS 
BEGIN
DECLARE @x int, @c varchar(200)
SELECT @x = charindex(',',@Str,1)
WHILE @x <> 0
	BEGIN
		SELECT @c = SUBSTRING(@Str,1,@x-1)
		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) 
		SELECT @Str = SUBSTRING(@Str,@x+1,LEN(@Str)-LEN(SUBSTRING(@Str,1,@x+1)))
		SELECT @x = charindex(',',@Str,1)
	END	
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) 

RETURN
END
GO

DECLARE @x varchar(8000)

SELECT @x = 'apples,bannas,cherries,dfruit,efruit'

SELECT * FROM dbo.udf_CSVTable(@x)

SELECT StringVal FROM udf_CSVTable('Jeff,Bill,Pete,Eddy, John,Mike')

GO


DROP FUNCTION dbo.udf_CSVTable
GO

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-10 : 13:56:37

Wait!

the final Insert isn't correct, it should be the remainder of @str...working to correct it now...

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-10 : 13:59:34

Here we go...100%



CREATE FUNCTION udf_CSVTable( @Str varchar(7000) ) 
	RETURNS @t TABLE (numberval int, stringval varchar(100), DateVal datetime) 
AS 
BEGIN
DECLARE @x int, @c varchar(200)
SELECT @x = charindex(',',@Str,1)
WHILE @x <> 0
	BEGIN
		SELECT @c = SUBSTRING(@Str,1,@x-1)
		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) 
		SELECT @Str = SUBSTRING(@Str,@x+1,LEN(@Str)-LEN(SUBSTRING(@Str,1,@x)))
		SELECT @x = charindex(',',@Str,1)
	END	
INSERT INTO @t 
VALUES (  CASE WHEN isnumeric(@Str)=1 THEN @c else Null END
	, rtrim(ltrim(@Str))
	, CASE WHEN isdate(@Str)=1 THEN @c else Null END) 

RETURN
END
GO

DECLARE @x varchar(8000)

SELECT @x = 'apples,bannas,cherries,dfruit,efruit'

SELECT * FROM dbo.udf_CSVTable(@x)

SELECT StringVal FROM udf_CSVTable('Jeff,Bill,Pete,Eddy, John,Mike')
GO


DROP FUNCTION dbo.udf_CSVTable
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources