Passing Lists to Stored Procedures

This code accepts a comma delimited list if values, such as accountnumvers and created a temporary table of them. That table, then is useful in IN clauses of WHERE clauses in sql statenments. The list of passed in values MUST be comma delimited, AND MUST end with a trailing comma:

CREATE PROCEDURE up_passlist( @strTarget varchar(50))
AS
/*
Passes a list of integer id s into a stored procedure

Though I’ve made this a stored procedure for demonstration sake, it is more properly
a snippet. The above code should preprocess more code as it sets up the temporary table
@tempTable, which contains the desired targets in the field, targetfield.

Then you can use this table in a join or in an IN clause to select the desired records from another object table later in the sproc.

The input parameter is a concatenated list of integers, separated by commas, and ending in a trailing comma.
*/
DECLARE @tempTable TABLE (targetfield int)
DECLARE @CurrentTarget int,
@NextTarget int,
@value int

SELECT @CurrentTarget = 0,
@NextTarget= CHARINDEX(',', @strTarget, @CurrentTarget)

WHILE (@NextTarget> 0)
BEGIN
SELECT @value = CONVERT(int, SUBSTRING(@strTarget, @CurrentTarget, @NextTarget - @CurrentTarget))
INSERT INTO @tempTable(targetfield) VALUES (@value)
SELECT @CurrentTarget = @NextTarget + 1
SELECT @NextTarget = CHARINDEX(',', @strTarget, @CurrentTarget)
END

select * from @tempTable
GO

Resources

•  The Guru's Guide to SQL Server Stored Procedures, XML, and HTML

This resource discusses aboutSql Server and Stored Procedures.

•  Information: Stored Procedure Programming

This resource is useful in understanding programming stored Procedures in SQL Server

Metro NY / NJ SQL Server Consultants

We specialize is custom database software. Call us for a free consultation (973) 635 0080 or email us at paladn.com

Database programming: Database Development
Computer Programming: Web Site Services
Computer Programming: Custom Software
Computer Consulting: IT Consulting
Paladin Consultants, Inc. Home Page
Computer Consulting: Contact Us

		Copyright © 2001 by Paladin Consultants,Inc. Chatham, NJ All Rights Reserved This page revised   May 23, 2006