Delimited String to Multiple Records In Stored Proc. 1

[Affleck]

Hi all I hope someone can help me.

I have a stored procedure that inserts data into multiple tables. I am wanting to parse a comma delimited string and insert new records for each element. i.e

@year1content = french,german,maths,ecnomics...
(from textarea form field)
@CourseID = @@IDENTITY (from autonumber in main table)

---------------------------------------------------------
SET @CourseID = @@IDENTITY

--insert into year1 Table

INSERT INTO Year1 (CourseID, Content)

VALUES (@CourseID, @year1content) -- I need a loop here?

Return

-------------------------------------
To give me:

Year1
-------------------
ID Content
------------------
2 french
2 german
2 maths
2 economics

Thanks in Advance.

 

[tlbroadbent]

Check the following link to download a stored procedure for parsing a comma delimited variable. You should be able to modify it for your use.

http://www.sqlteam.com/item.asp?ItemID=637

Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[Affleck]

Thanks, Ive integrated the code into my SP but im getting errors ( error converting Varchar to Int) and I cant seem to fix them. My SP looks like this now: There maybe an obvious error as Im very new to all this



CREATE PROCEDURE spAddCourse

--Declare variables to be inserted into 6 tables.

(@separator [char] (1), @year1content [varchar] (255), @CertificationID [int], @CourseName [varchar] (255), @UcasCode [varchar] (255), @SchoolID [int], @ModeID [int], @Duration [varchar] (255), @Courseinfo [varchar] (4000), @Coursework [varchar] (4000), @TransferOpps [varchar] (2000), @Accreditation [varchar] (2000), @Careers [varchar] (2000), @telephone [varchar] (255), @Fax [varchar] (255), @email [varchar] (100), @CompulsoryReq [varchar] (255), @StandardEntry [varchar] (255), @GCE_VCE [varchar] (500), @EdExcel [varchar] (300), @ScotHigher [varchar] (300), @IrishHigher [varchar] (255), @HEFC [varchar] (255), @other [varchar] (500), @additional [varchar] (300), @CourseID [int] OUTPUT)

AS

--insert into Courses Table

INSERT INTO Courses ( [CertificationID], [CourseName], [UcasCode], [SchoolID], [ModeID], [Duration], [Courseinfo], [Coursework], [TransferOpps], [Accreditation], [Careers], [telephone], [Fax], , [CompulsoryReq], [StandardEntry], [GCE_VCE], [EdExcel], [ScotHigher], [IrishHigher], [HEFC], [Other], [AdditionalNotes] )

VALUES (@CertificationID, @CourseName, @UcasCode, @SchoolID, @ModeID, @Duration, @Courseinfo, @Coursework, @TransferOpps, @Accreditation, @Careers, @telephone, @Fax, @email, @CompulsoryReq, @StandardEntry, @GCE_VCE, @EdExcel, @ScotHigher, @IrishHigher, @HEFC, @other, @additional)

SET @CourseID = @@IDENTITY

--start loop to insert multiple child records from string.

set nocount on

set @separator = ','

-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
declare @separator_position int -- This is used to locate each separator character
declare @array_value varchar(1000) -- this holds each array value as it is returned

-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @year1content = @year1content + @separator

-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @year1content) <> 0
begin

-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @year1content)
select @array_value = left(@year1content, @separator_position - 1)

-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
INSERT INTO Year1 (CourseID, Content)

VALUES (@CourseID, @array_value)

-- This replaces what we just processed with and empty string

select @year1content = stuff(@year1content, 1, @separator_position, '')
end

set nocount off