Here is a section of a stored procedure I wrote that accepts a list then
parses it apart. The purpose of this routine was to accept 2 lists. The
first one is a list of part numbers (i.e. 1,2,3,4) and the 2nd is a list of
divisions (i.e. E,P,E,M). Both lists have the same number of items in them.
The lists are then parsed and each item is inserted into the passed table.
This table can then be used in the calling stored procedure for further
processing.
This procedure works in both SQL2000 and SQL7.
CREATE PROCEDURE rMakePartDivListTable
@tcPartDivTable VARCHAR(500) = NULL,
@tcPartList VARCHAR(300) = NULL,
@tcDivList VARCHAR(200) = NULL
AS
SET NOCOUNT ON
DECLARE @RetVal INT,
@lcAddlInfo VARCHAR(250),
@lcSQL VARCHAR(5000),
@lcPartWork varchar(300),
@lcDivWork varchar(200),
@lnCommaPos int,
@lcPart varchar(5),
@lcDivno varchar(5)
SET @lcPartWork = @tcPartList
SET @lcDivWork = @tcDivList
WHILE LEN(@lcPartWork) > 0 AND LEN(@lcDivWork) > 0
BEGIN
SET @lnCommaPos = CHARINDEX(',', @lcPartWork)
IF @lnCommaPos > 0
BEGIN
SET @lcPart = SUBSTRING(@lcPartWork, 1, @lnCommaPos
- 1)
SET @lcPartWork = SUBSTRING(@lcPartWork, @lnCommaPos
+ 1, LEN(@lcPartWork) - @lnCommaPos)
END
ELSE
BEGIN
SET @lcPart = @lcPartWork
SET @lcPartWork = ''
END
SET @lnCommaPos = CHARINDEX(',', @lcDivWork)
IF @lnCommaPos > 0
BEGIN
SET @lcDivno = SUBSTRING(@lcDivWork, 1, @lnCommaPos
- 1)
SET @lcDivWork = SUBSTRING(@lcDivWork, @lnCommaPos +
1, LEN(@lcDivWork) - @lnCommaPos)
END
ELSE
BEGIN
SET @lcDivno = @lcDivWork
SET @lcDivWork = ''
END
SET @lcSQL = 'INSERT INTO ' + @tcPartDivTable
+ ' VALUES (''' + @lcPart + ''', '
+ '''' + @lcDivno + ''')'
EXEC(@lcSQL)
SET @RetVal = @@ERROR
IF @RetVal != 0
BEGIN
SET @lcAddlInfo = 'rMakePartDivListTable Error: '
+ CONVERT(varchar,
@RetVal)
EXECUTE EUCommon.dbo.sp_euerror 50003, 23, @lcAddlInfo
END
END
RETURN @RetVal
Bill Grover
Supervisor, IS
EU Services, Inc.
649 N Horners Ln
Rockville MD 20850
Phone: 301-424-3300 x396
FAX: 301-424-3300 x1396#
E-Mail: [EMAIL PROTECTED]
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, August 23, 2001 6:22 AM
> To: [EMAIL PROTECTED]
> Subject: CF-Talk-list V1 #113
>
>
> CF-Talk-list Thu, 23 Aug 2001
> Volume 1 : Number 113
>
>
> Date: Thu, 23 Aug 2001 13:05:11 +1000
> From: "Andrew Scott" <[EMAIL PROTECTED]>
> Subject: RE: Passing list to Stored Procedure..
> Message-ID: <000001c12b80$6ce72360$0101a8c0@voyager>
>
> Any chance of an example on how to do this for us not so gurus of
> SQL/PL..
>
>
> -----Original Message-----
> From: Don Vawter [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, 23 August 2001 12:02 PM
> To: CF-Talk
> Subject: Re: Passing list to Stored Procedure..
>
> i routinely pass a comma delimited list to SQL Server stored procs. I
> just
> wrote a few simple udfs in sql server to parse the list when
> it gets it.
>
> ----- Original Message -----
> From: "Darryl Lyons" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Wednesday, August 22, 2001 4:50 PM
> Subject: SOT: Passing list to Stored Procedure..
>
>
> > Does anyone know if you can pass a list of values to a stored
> procedure
> via
> > CF? This is mainly needed because the list values would be
> drawn from
> an
> > unspecified datasource (e.g. a list of user groups stored
> in a session
> > var)..
> >
> > -----------------------------------
> > Regards,
> >
> > Darryl Lyons
> > Senior Systems Developer
> > Fuzion: http://www.fuzion.com.au
> >
> > Email : [EMAIL PROTECTED]
> > Phone : (07) 3620 1000
> > Fax : (07) 3620 1001
> > Technical Support : 1300 888 480
> >
> > 'merging business + technology'
> >
> >
> >
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Structure your ColdFusion code with Fusebox. Get the official
> book at http://www.fusionauthority.com/bkinfo.cfm
>
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
