WelcomeServicesPortfolioKnowledge BaseContact Us
 27 Seconds, Inc.


Knowledge base View Article

need hosting?

Need hosting? We use and love our host - CrystalTech.com!
Need hosting? We use and love our host - CrystalTech.com!

Text Disguise - CAPTCHA-image/Human Interactive Proof web services

National Holiday Dates and Bank Holiday Dates - web site that provides the dates of national and bank holidays for the United States, United Kingdom, Ireland and Scotland

Holiday Web Service - web services for programmers to provide the dates of national and bank holidays in their applications for the United States, United Kingdom, Ireland and Scotland

Our Family Heart - web site to help families communicate no matter where they are

What?! You haven't heard about the greatest remote controlled flyer to come out in years?! Checkout the FlyTech DragonFly now!

Pass comma delimited list to stored proc.

Summary: To use a comma delimited list in a stored proc. used to mean that you had to dynamically create a SQL statement and execute it. Here's a way to get around that with SQL Server 2000.
 
Keywords: COMMA
COMMA SEPARATED VALUES
CSV
IN
IN CLAUSE
SEPARATED
SP
STORED PROC
VALUES
VARIABLES
Applicable Software: Active Server Pages (ASP)
Active Server Pages .NET (ASP.NET)
ADO
Microsoft SQL Server
 
Body:

To use a comma delimited list in a stored proc. used to mean that you had to dynamically create a SQL statement and execute it. Here's a way to get around that with SQL Server 2000.

In SQL, I created a TableFromCSV function. The function takes a comma separated string as input and returns a table: 

CREATE FUNCTION fnTableFromCSV
(
	@theString varchar(1000),
	@separator char(1)
)
RETURNS @Values TABLE (value INT)
AS
BEGIN
	DECLARE @seppos INT
	DECLARE @curval VARCHAR(1000)

	SET @theString = @theString + @separator
	WHILE PATINDEX('%' + @separator + '%' , @theString) <> 0
		BEGIN
			SELECT @seppos =  PATINDEX('%' + @separator + '%' , @theString)
			SELECT @curval = LEFT(@theString, @seppos - 1)
			INSERT @Values VALUES (@curval)

			SELECT @theString = STUFF(@theString, 1, @seppos, '')
		END
	RETURN
END

You can use it in a stored procedure like this: 

CREATE PROC spWhatever
(
	@TheStringWithIDs VARCHAR(1000)
)

SELECT *
FROM TheTable
WHERE SomeID IN ( SELECT * FROM fnTableFromCSV(@TheStringWithIDs, ',') )

 
Author: Dutch
Posted On: 11/22/2002 9:25:53 AM

Rate this article: Average: 8.5
n/a12345678910
Comments?

Article Search   |   All Articles

 

 
©2002 27 Seconds, Inc. All Rights Reserved.