Pass a list of integers to a stored proc - found a way

Dev Shed Forums Sponsor:

You don't need a fax machine to get faxes. Get a fax-to-email fax number from CallWave. Try it free.

January 19th, 2004, 09:07 AM

madmatt75

Junior Member

Join Date: Dec 2003

Location: Binghamton, NY

Posts: 22

Time spent in forums: < 1 sec
Reputation Power: 0

Pass a list of integers to a stored proc - found a way

I've always been bothered by not being able to pass a comma delimited list into a stored proc to be used in a subquery.

Lately I've been getting into temp tables and this morning I dreamed up the idea of parsing the list, loading it into a local temp table then using the temp table in a subquery.

If anyone knows of a reason that this isn't a good idea, especially in a multi-user, web based system then I'd like to hear it. If you think it's a good idea then feel free to grab the enclosed code and use it.

Thanks,
Matt.

create procedure tmpAccountTest
@NumberList varchar(100)
as
declare @oneNumber int
declare @POS int
if @NumberList <> ''
begin
--Step through the comma delimted list of numbers and plug each one into a temp table
create table #AccountList (AccountID int)
select @POS = patindex('%,%',@NumberList)
while @POS > 0
begin
select @oneNumber = cast(left(@NumberList,@POS - 1) as int)
insert into #AccountList (AccountID) values (@oneNumber)
select @NumberList = right(@NumberList,len(@NumberList) - @POS)
select @POS = patindex('%,%',@NumberList)
end
--do the last one which doesn't have any comma
select @oneNumber = cast(@NumberList as int)
insert into #AccountList (AccountID) values (@oneNumber)
end
--now test:
select * from test where fld1 in (select accountid from #AccountList)

January 19th, 2004, 11:27 PM

victorpendleton

Contributing User

Join Date: Jan 2003

Location: No es importante

Posts: 2,065

Time spent in forums: 6 h 31 m 56 sec
Reputation Power: 8

Did not review your code but why are you not able to pass your comma delimited list to your stored procedure? You would just need to parse the recieved parameter for each instance of the delimiter. I use the pipe `|` symbol for such instances. I am not knocking your efforts just curious as to the issue you had.

__________________
El éxito consiste en una serie de pequeñas victorias día a día

MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html

January 20th, 2004, 06:43 AM

madmatt75

Junior Member

Join Date: Dec 2003

Location: Binghamton, NY

Posts: 22

Time spent in forums: < 1 sec
Reputation Power: 0

To use within a subquery

select * from MyTable where [id] in (1,2,3,4,5)

Parsing them out is fine, but then what? If you have an alternative way, please fill me in.

Matt.

January 20th, 2004, 07:43 AM

abombss

Contributing User

Join Date: Jan 2004

Posts: 49

Time spent in forums: < 1 sec
Reputation Power: 5

Try this page. I stumbled accross it a couple weeks ago and thought it was great!

http://vyaskn.tripod.com/passing_ar..._procedures.htm

Adam

January 20th, 2004, 07:49 AM

abombss

Contributing User

Join Date: Jan 2004

Posts: 49

Time spent in forums: < 1 sec
Reputation Power: 5

Try this page. I stumbled accross it a couple weeks ago and thought it was great!

http://vyaskn.tripod.com/passing_ar..._procedures.htm

A SPROC without a temp table

Code:

CREATE PROC dbo.GetOrderList3
(
	@OrderList varchar(500)
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @TempList table
	(
		OrderID int
	)

	DECLARE @OrderID varchar(10), @Pos int

	SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
	SET @Pos = CHARINDEX(',', @OrderList, 1)

	IF REPLACE(@OrderList, ',', '') <> ''
	BEGIN
		WHILE @Pos > 0
		BEGIN
			SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
			IF @OrderID <> ''
			BEGIN
				INSERT INTO @TempList (OrderID) VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
			END
			SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
			SET @Pos = CHARINDEX(',', @OrderList, 1)

		END
	END	

	SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
	FROM 	dbo.Orders AS o
		JOIN 
		@TempList t
		ON o.OrderID = t.OrderID
		
END
GO

GRANT EXEC ON dbo.GetOrderList3 TO WebUser
GO

A UDF.

Code:

--The following is a general purpose UDF to split comma separated lists into individual items.
--Consider an additional input parameter for the delimiter, so that you can use any delimiter you like.
CREATE FUNCTION dbo.SplitOrderIDs
(
	@OrderList varchar(500)
)
RETURNS 
@ParsedList table
(
	OrderID int
)
AS
BEGIN
	DECLARE @OrderID varchar(10), @Pos int

	SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
	SET @Pos = CHARINDEX(',', @OrderList, 1)

	IF REPLACE(@OrderList, ',', '') <> ''
	BEGIN
		WHILE @Pos > 0
		BEGIN
			SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
			IF @OrderID <> ''
			BEGIN
				INSERT INTO @ParsedList (OrderID) 
				VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
			END
			SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
			SET @Pos = CHARINDEX(',', @OrderList, 1)

		END
	END	
	RETURN
END
GO

CREATE PROC dbo.GetOrderList6
(
	@OrderList varchar(500)
)
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT 	o.OrderID, CustomerID, EmployeeID, OrderDate
	FROM	dbo.Orders AS o
		JOIN
		dbo.SplitOrderIDs(@OrderList) AS s
		ON
		o.OrderID = s.OrderID
END
GO

GRANT EXEC ON dbo.GetOrderList6 TO WebUser
GO

Adam

January 21st, 2004, 10:26 AM

madmatt75

Junior Member

Join Date: Dec 2003

Location: Binghamton, NY

Posts: 22

Time spent in forums: < 1 sec
Reputation Power: 0

The article's author is doing the same thing as me except instead of a temporary table (#MyTable) using a table variable (@MyTable).

SQL's help for a table variable reads "Use table variables instead of temporary tables, whenever possible. " So thats what I'll do.

Thanks for the info.

February 13th, 2004, 06:23 PM

cvsherri

Registered User

Join Date: Feb 2004

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

Here is a FUNCTION I developed bassed on your code above, thanks all

Code:

CREATE FUNCTION dbo.ListToCol 
	(@List varchar(1000),
         @Delimiter varchar(2)
         )
RETURNS @TempList TABLE 
	(ListVar varchar(50))
AS
BEGIN

	DECLARE @OrderID varchar(50), @Pos int

	SET @List = LTRIM(RTRIM(@List))+ @Delimiter
	SET @Pos = CHARINDEX(',', @List, 1)

	IF REPLACE(@List, @Delimiter, '') <> ''
	BEGIN
		WHILE @Pos > 0
		BEGIN
			SET @OrderID = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
			IF @OrderID <> ''
			BEGIN
				INSERT INTO @TempList (ListVar) VALUES (CAST(@OrderID AS varchar(50))) --Use Appropriate conversion
			END
			SET @List = RIGHT(@List, LEN(@List) - @Pos)
			SET @Pos = CHARINDEX(@Delimiter, @List, 1)

		END
	END	

	RETURN
		
END
GO

February 13th, 2004, 06:28 PM

cvsherri

Registered User

Join Date: Feb 2004

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

Thanks Again!

Opps! posted too soon...

Here is the practical application of the function.

Code:

SELECT  cast(eID AS varchar) + ' - ' + eName
FROM  events 
WHERE eID in (SELECT * FROM  dbo.ListToCol('15,17',','))

February 16th, 2004, 11:47 AM

bemall

Registered User

Join Date: Feb 2004

Posts: 3

Time spent in forums: 8 m 54 sec
Reputation Power: 0

passing a list to a stored proc

I do have an exact similar sitiuation; a multi user web application running on SQL backend. In my sitiuation the list that was to be passed determined what modules in the application the user has access to meaning that this query is executed each time a user access any page. This would result in too many temp tables with no guarantee on the uniqueness besides being a resource hog.

My solution:
I created two stored procedures.
proc1 receives the comma delimited list in the form a,b,c,d,e,f.
This cannot be used in the "IN()" clause before formating.
I pass this list to proc2 which is called within proc1. Proc2 formats the list by using the string function "replace". Replacing all cormas with ',' and adding ' at the beginning and end of the string hence the new list 'a','b','c','d'.
This list is passed as an output parameter to proc1 which uses it in the query.

contains/calls proc2 and also has the query that uses results from proc2.

Viewing: Dev Shed Forums > Databases > MS SQL Development > Pass a list of integers to a stored proc - found a way

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

Accelerating Trading Partner Performance
One in five. That's how many partner transactions have at least one error. That is an amazing statistic, particularly given the extraordinary leaps in innovation across the global supply chain during the past two decades. Download this white paper to learn more.

Competing on Analytics
This Tech Analysis is designed to help identify characteristics shared by analytics competitors, and includes information about 32 organizations that have made a commitment to quantitative, fact-based analysis.

Cost Effective Scaling with Virtualization and Coyote Point Systems
An overview of the industry trend toward virtualization, how server consolidation has increased the importance of application uptime and the steps being taken to integrate load balancing technology with virtualized servers.

Five Checkpoints to Implementing IP Telephony
Implementation planning for IP PBX software and IP telephony has become vital as businesses replace discontinued legacy PBX phone systems. This informative whitepaper outlines five "checkpoints" for any implementation plan that will help make IP communications a successful proposition.

Hosted Email Security: Staying Ahead of New Threats
In the last two years, email has become a fierce battleground between the nefarious forces of spam and malware, and the heroes of messaging protection. The spam volumes increased alarmingly every month, bringing clever new forms of phishing and virus propagation attacks.

More Free IT White Papers!