tlbroadbent - Still need help please error converting Varchar to Int

[Affleck]

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

 

[rajeevnandanmishra]

Hi Affleck,

It seems that the error is coming just because of following block
--------------------
create procedure (....) as
insert into course(...)
values(...)
--------------------
Some where you are trying to put character data into an int column. This may be because you had defined a wrong datatype while defining parameters or may be because you are passing a wrong data type to the procedure.

Just check this. And if problem persist, i suggest, you to give the table structure of [course] and the statement by which you are calling this procedure with parametes.

 

[Affleck]

Ive checked all my Parameters and my Data types and they all seem to match??? The problem only started to happen when i introduced this loop into my SP >>

set @CourseID = @@IDENTITY
set nocount on

select @separator = '|'

-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
-- This is used to locate each separator character


-- 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
set @separator_position = patindex('%' + @separator + '%' , @year1content)
set @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

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

set nocount off