Server: Msg 512, Level 16, State 1 - Error Cannot Figure it out 1

[bombplayer]

When running an insert command I am receiving the subject error. I have tried to change some things in the code to reference ideas that I found on the net but to no avail. Can anyone help?


Create Procedure sp_saPostPOSData

As



Set nocount on


Declare @C_SyStudentID int,
@C_AdEnrollID int,
@C_Source char,
@C_Discount int,
@C_Payments int,
@C_FaPmtPeriodID int,
@C_Academicyear int,
@C_Sentback int,
@C_sabankdepid int,
@C_commrate int,
@C_commpaid int,
@C_glpost int,
@C_fastudentaid int,
@C_totalizer int ,
@C_salesdate datetime,
@C_units int,
@C_totalsales money,
@C_sabillcode char(3),
@C_sycampusid int,
@C_descrip char(40),
@C_type char(4),
@C_amount money,
@C_status char(1),
@C_amrepid int,
@C_adduserid int,
@C_userid int,
@C_date datetime,
@C_postdate datetime,
@C_dateadded datetime,
@C_datelstmod datetime,
@C_ref char,
@C_adtermid int

Declare @MyTran int -- Next Transaction Number


Declare C Cursor For
Select SyStudentID,
AdEnrollID,
Source,
Discount,
Payments,
FaPmtPeriodID,
Academicyear,
Sentback,
sabankdepid,
commrate,
commpaid,
glpost,
fastudentaidid,
totalizer,
salesdate,
units,
totalsales,
sabillcode,
sycampusid,
descrip,
type,
amount,
status,
amrepid,
adduserid,
userid,
date,
postdate,
dateadded,
datelastmod,
ref,
adtermid From POSTable

Open C

Fetch Next From C
Into @C_SyStudentID,
@C_AdEnrollID,
@C_Source,
@C_Discount,
@C_Payments,
@C_FaPmtPeriodID,
@C_Academicyear,
@C_Sentback,
@C_sabankdepid,
@C_commrate,
@C_commpaid,
@C_glpost,
@C_fastudentaid,
@C_totalizer,
@C_salesdate,
@C_units,
@C_totalsales,
@C_sabillcode,
@C_sycampusid,
@C_descrip,
@C_type,
@C_amount,
@C_status,
@C_amrepid,
@C_adduserid,
@C_userid,
@C_date,
@C_postdate,
@C_dateadded,
@C_datelstmod,
@C_ref,
@C_adtermid

While @@Fetch_Status = 0
BEGIN

-- Update SyStudent (NextTranNum, ARBalance)
IF (@C_Totalizer in ('5001','5002','5003','5004','5005','5006','5007','5008','5009'))
Update SyStudent WITH (ROWLOCK)
Set ArBalance = (ArBalance + @C_TotalSales),
NextTranNum = NextTranNum + 1,
DateLstMod = GetDate()
Where SyStudentID in (@C_SyStudentID)
IF (@C_Totalizer in ('1201','1202','1203','1204','1205','1206','1230','1231','1232','1233'))
Update SyStudent WITH (ROWLOCK)
Set ArBalance = (ArBalance - @C_TotalSales),
NextTranNum = NextTranNum + 1,
DateLstMod = GetDate()
Where SyStudentID in (@C_SyStudentID)
IF (@C_Totalizer in ('22','26','30','34'))
Update SyStudent WITH (ROWLOCK)
Set ArBalance = (ArBalance + @C_TotalSales),
NextTranNum = NextTranNum + 1,
DateLstMod = GetDate()
Where SyStudentID in (@C_SyStudentID)

-- Get new transaction Number
Select @MyTran = NextTranNum
From SyStudent (ROWLOCK)
Where SyStudentID in (@C_SyStudentID)

-- Update AdEnroll (ARBalance)
IF (@C_Totalizer in ('5001','5002','5003','5004','5005','5006','5007','5008','5009'))
Update AdEnroll WITH (ROWLOCK)
Set ArBalance = (ArBalance + @C_TotalSales)
Where AdEnrollID in (@C_AdEnrollID)
IF (@C_Totalizer in ('1201','1202','1203','1204','1205','1206','1230','1231','1232','1233'))
Update AdEnroll WITH (ROWLOCK)
Set ArBalance = (ArBalance - @C_TotalSales)
Where AdEnrollID in (@C_AdEnrollID)
IF (@C_Totalizer in ('22','26','30','34'))
Update AdEnroll WITH (ROWLOCK)
Set ArBalance = (ArBalance + @C_TotalSales)
Where AdEnrollID in (@C_AdEnrollID)



-- Insert New Row into SaTrans
Insert SaTrans WITH (ROWLOCK)
(SyStudentID,
SyCampusID,
AdEnrollID,
Source,
Type,
TranNum,
Descrip,
Status,
Date,
PostDate,
Amount,
PaymentType,
FaPmtPeriodID,
AdTermID,
SaBankAccountID,
Ref,
Academicyear,
Sentback,
sabankdepid,
commrate,
commpaid,
glpost,
fastudentaidid,
sabillcode,
amrepid,
adduserid,
userid,
dateadded,
datelstmod)

Select @C_SyStudentID,
@C_SyCampusID,
@C_AdEnrollID,
@C_Source,
@C_type,
@MyTran,
@C_descrip,
@C_status,
@C_date,
@C_postdate,
@C_amount,
' ',
@C_FaPmtPeriodID,
@C_adtermid,
' ',
@C_ref,
@C_Academicyear,
@C_Sentback,
@C_sabankdepid,
@C_commrate,
@C_commpaid,
@C_glpost,
@C_fastudentaid,
@C_sabillcode,
@C_amrepid,
@C_adduserid,
@C_userid,
@C_dateadded,
@C_datelstmod from POSTable


-- Get Next Row
Fetch Next From C
Into @C_SyStudentID,
@C_AdEnrollID,
@C_Source,
@C_Discount,
@C_Payments,
@C_FaPmtPeriodID,
@C_Academicyear,
@C_Sentback,
@C_sabankdepid,
@C_commrate,
@C_commpaid,
@C_glpost,
@C_fastudentaid,
@C_totalizer,
@C_salesdate,
@C_units,
@C_totalsales,
@C_sabillcode,
@C_sycampusid,
@C_descrip,
@C_type,
@C_amount,
@C_status,
@C_amrepid,
@C_adduserid,
@C_userid,
@C_date,
@C_postdate,
@C_dateadded,
@C_datelstmod,
@C_ref,
@C_adtermid
END
Close C
Deallocate C


return(0)

ErrTag:
return(1)




GO

 

[tlbroadbent]

Modify the INSERT to use VALUES rather than SELECT FROM.

-- Insert New Row into SaTrans
Insert SaTrans WITH (ROWLOCK)
(SyStudentID, SyCampusID, AdEnrollID, Source, Type,
TranNum, Descrip, Status, Date, PostDate, Amount,
PaymentType, FaPmtPeriodID, AdTermID, SaBankAccountID,
Ref, Academicyear, Sentback, sabankdepid, commrate,
commpaid, glpost, fastudentaidid, sabillcode, amrepid,
adduserid, userid, dateadded, datelstmod)

Values
(@C_SyStudentID, @C_SyCampusID, @C_AdEnrollID, @C_Source, @C_type,
@MyTran, @C_descrip, @C_status, @C_date, @C_postdate, @C_amount,
' ', @C_FaPmtPeriodID, @C_adtermid, ' ',
@C_ref, @C_Academicyear, @C_Sentback, @C_sabankdepid, @C_commrate,
@C_commpaid, @C_glpost, @C_fastudentaid, @C_sabillcode, @C_amrepid,
@C_adduserid, @C_userid, @C_dateadded, @C_datelstmod) Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[bombplayer]

Worked like a charm. Thanks so much Terry