June 11, 2018 at 8:18 am
Hello,
I'm receiving this error when I am executing my stored procedure:
Msg 245, Level 16, State 1, Procedure ad_StatChange_EnrolltoBP, Line 73
Conversion failed when converting the varchar value 'INVAL ' to data type int.
I'm looking at my WHERE statements and I'm not seeing the issue.
CREATE PROCEDURE dbo.ad_StatChange_EnrolltoBP
-- Add the parameters for the stored procedure here
@user-id int = 1,
@ReasonID int = 0,
@NewStatus int = 6
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #DocVal
(systudentid int
)
CREATE TABLE #ATBV
(systudentid int
,score int
)
CREATE TABLE #ATBQ
(systudentid int
,score int
)
CREATE TABLE #WONDVAL
(systudentid int
,AdProgramID int
,AmPrevEducID int
,Score int
,ATBVscore int
,ATBQscore int
,MinScore int
,ATBvMin int
,ATBqMin int
)
CREATE TABLE #FINAL
(systudentid int
,adenrollid int
)
--Fill DocValue --------------- Document Validation
INSERT INTO #DocVal
SELECT DISTINCT cd.Systudentid
FROM CmDocument cd
INNER JOIN systudent st On cd.SyStudentID = st.SyStudentId
WHERE st.SySchoolStatusID In (88) AND ((CmDocTypeID In (141,150) and CmDocStatusID IN (13)) OR ((CmDocTypeID In (141,412) and CmDocStatusID IN (13)))) AND cd.DateApproved IS NOT NULL
AND CAST((SELECT TOP 1 cd1.DateApproved FROM CmDocument cd1 WHERE cd1.SyStudentID = cd.SyStudentID AND cd1.CmDocTypeID IN (141) and CmDocStatusID IN (13) ORDER BY cd1.DateApproved DESC) as date)
>= CAST(DATEADD(d,-30,(SELECT TOP 1 ssc.DateAdded FROM systatchange ssc WHERE ssc.SyStudentID = cd.SyStudentID AND ssc.NewSySchoolStatusID = 88 ORDER BY DateAdded DESC)) as date)
--Fill WondVal --------------- Wonderlic Validation
INSERT INTO #ATBV
SELECT systudentid, MAX(score) score
FROM AmProspectTest
WHERE amtestID IN (45,48,356,357)
GROUP BY systudentid
INSERT INTO #ATBQ
SELECT systudentid, MAX(score) score
FROM AmProspectTest
WHERE amtestID IN (46,47,354,355)
GROUP BY systudentid
INSERT INTO #WONDVAL
SELECT DISTINCT st.systudentid
,st.AdProgramID
,st.AmPrevEducID
,ISNULL((SELECT TOP 1 apt.score FROM AmProspectTest apt
WHERE apt.SyStudentID = st.SyStudentId AND amtestid in (88,89,90,91,93,95)
ORDER BY apt.score desc),0) Score
,ISNULL(#ATBV.score,0) ATBVscore
,ISNULL(#ATBQ.score,0) ATBQscore
,CASE WHEN pt.AmTestID IN (63) OR pt.Score IS NULL OR pt.score = 0
THEN 0
WHEN st.AmPrevEducID IN (6,7)
THEN 0
WHEN st.adprogramID in (84)
THEN 21
WHEN st.adprogramid IN (80)
THEN 20
WHEN st.adprogramid IN (62,103,105,125)
THEN 17
WHEN st.adprogramid IN (117) AND st.SyCampusID IN (15)
THEN 14
WHEN st.adprogramid IN (114,102,76,34)
THEN 13
ELSE 10
END AS MinScore
,CASE WHEN st.SyCampusID NOT IN (10) and st.AmPrevEducID IN (6,7)
THEN 200
WHEN st.SyCampusID IN (10) and st.AmPrevEducID IN (6,7)
THEN 220
END AS ATBvMin
,CASE WHEN st.SyCampusID NOT IN (10) and st.AmPrevEducID IN (6,7)
THEN 210
WHEN st.SyCampusID IN (10) and st.AmPrevEducID IN (6,7)
THEN 220
END AS ATBqMin
FROM systudent st
LEFT OUTER JOIN AmProspectTest pt ON st.SyStudentId = pt.SyStudentID
LEFT OUTER JOIN #ATBV ON st.SyStudentId = #ATBV.SyStudentID
LEFT OUTER JOIN #ATBQ ON st.SyStudentId = #ATBQ.SyStudentID
WHERE syschoolstatusid in (88)
-- Final Student List
INSERT INTO #FINAL
SELECT dv.systudentid
,ae.AdEnrollID
FROM #DocVal dv
INNER JOIN #WONDVAL wv ON dv.systudentid = wv.systudentid
INNER JOIN systudent st (NOLOCK) ON dv.systudentID = st.SyStudentId
LEFT OUTER JOIN AdEnroll ae (NOLOCK) ON st.SyStudentId = ae.SyStudentID and ae.AdProgramID = st.AdProgramID
WHERE (wv.score = 0 AND wv.MinScore = 0)--Exempt
OR (wv.AmPrevEducID IN (6,7) AND wv.ATBQscore >= wv.ATBqMIN AND wv.ATBVscore >= wv.ATBvMIN) --ATB
OR (wv.AmPrevEducID NOT IN (6,7) AND wv.Score >= wv.MinScore) --Wonderlic
DECLARE @studentID int, @EnrollID int;
SELECT @studentID = f.systudentid,
@EnrollID = f.adenrollid
FROM #FINAL f
--Create status change
INSERT INTO dbo.SyStatChange
(systudentid
,[AdEnrollID]
,[Comment]
,[EffectiveDate]
,[NewSyStatusID]
,[PrevSyStatusID]
,[NewSySchoolStatusID]
,[PrevSySchoolStatusID]
,[InternalComm]
,[Status]
,[ModFlag]
,[CmEventID]
,[UserID]
,[DateAdded]
,[DateLstMod]
,[AdReasonID]
,[Type]
)
SELECT DISTINCT f.systudentid
,ae.[AdEnrollID]
,'Automated Status Change'[Comment]
,Getdate()[EffectiveDate]
,6 [NewSyStatusID]
,4 [PrevSyStatusID]
,6 [NewSySchoolStatusID]
,88 [PrevSySchoolStatusID]
,'New Enrollment' [InternalComm]
,'P' [Status]
,'A' [ModFlag]
,0 [CmEventID]
,1 [UserID]
,Getdate() [DateAdded]
,Getdate() [DateLstMod]
,0 [AdReasonID]
,'S' [Type]
FROM #FINAL f
INNER JOIN systudent st (NOLOCK) ON f.systudentID = st.SyStudentId
LEFT OUTER JOIN AdEnroll ae (NOLOCK) ON st.SyStudentId = ae.SyStudentID and ae.AdProgramID = st.AdProgramID
-- Update the enrollment record with the new status
Update dbo.AdEnroll
Set SySchoolStatusID = @NewStatus,
StatusDate = Getdate(),
UserID = @user-id,
DateLstMod = GetDate(),
AdReasonID = CASE WHEN @ReasonID > 0 THEN @ReasonID ELSE AdReasonID END
Where AdEnrollID = @EnrollID
-- Update the Student Master
Exec dbo.c2ksp_AdStatusChange_UpdateStudent @StudentID, @user-id
END
June 11, 2018 at 1:23 pm
You're treating everything as INT, but there must be a VARCHAR field in there somewhere. Here are the fields you are treating as INT - are they all in fact INT datatype?
systudent
SyStudentId
SySchoolStatusID
SyCampusID
AmPrevEducID
CmDocument
SyStudentID
CmDocTypeID
CmDocStatusID
systatchange
SyStudentID
NewSySchoolStatusID
AmProspectTest
SyStudentID
AmTestID
score
AdEnroll
SyStudentID
AdProgramID
SySchoolStatusID
UserID
AdReasonID
AdEnrollID
June 11, 2018 at 3:45 pm
Found it score was a char(8). I missed that. Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply