"hals_left" <cc900***@ntu.ac.uk> wrote in message
news:1124452462.670075.99190@f14g2000cwb.googlegroups.com...
> Hi, I have a stored procedure that has the following stages -
>
> 1,Start a Transaction
> 2,Retrieves a Primary key of a customer based on a sales ID
> 3,Create a master Invoice record
> 4,Retrieves the Primary Key value of the Invoice
> 5,Create n Invoice Lines
> 6,Updates sales records to show billing done
> 7,Checks for errors and either Commits or Rolls back the transaction
>
> I had a bug in the code that creates invoice lines because the input
> paramters of line descriptions & values was a comma-separated list and
> the udf split function being used had a limit to 1000 characters per
> list.
>
> What was happening was that Invoice master record was created (step 3),
> and sales were updated (step 6) but no invoice lines were being created
> (step 5).
>
> I cant understand why the final error handler wasnt catching this and
> rolling back the transactions. Can anyonme explain or show a better way
> to do this procedure.
>
> Thanks
> hals_left
>
>
>
>
>
>
>
> CREATE Procedure dbo.AddInvoice
>
> @Course smallint,
> @Total Smallint,
> @User Smallint,
>
> @csvDescriptions Varchar(2000),
> @csvQuantities Varchar(2000),
> @csvValues Varchar(2000),
>
> -- Sage Codes
> @csvDepartments Varchar(2000),
> @csvNominals Varchar(2000),
>
> @CustomerPO varchar(15),
>
> -- 'SI' or 'SC'
> @TType char(2),
> @Status smallint OUTPUT
>
>
> AS
>
> Declare @InvoiceID Smallint
> Declare @Centre smallint
>
>
> BEGIN TRANSACTION
>
> -- Get the customer ID
> SET @Centre=(SELECT CentreID  FROM Courses WHERE CourseID=@Course)
>
>
> -- Create the Master Record
> -- with the default status of 2= Invoicable
> INSERT INTO tblInvoice (Course, Centre, TotalAmount,CreatedBy, Status,
> CustomerPO)
> Values ( @Course, @Centre,@Total, @User,2,@CustomerPO )
>
>
> -- Get the ID of the master record
> SET @InvoiceID=@@Identity
>
>
> -- Create the invoice line  records
> INSERT INTO  tblInvoiceLine ( Invoice,TransactionType,
> [Description],Quantity,UnitCost,Department,Nominal )
> SELECT @InvoiceID,@TType,A.Value, B.Value, C.Value , D.Value, E.Value
> FROM dbo.Split(@csvDescriptions,',')  A
> JOIN dbo.Split(@csvQuantities,',') B ON  A.id = B.id
> JOIN dbo.Split(@csvValues,',')  C ON B.id = C.id
> JOIN dbo.Split(@csvDepartments,',')  D ON C.id = D.id
> JOIN dbo.Split(@csvNominals,',')  E ON D.id = E.id
>
> -- Update the unit enrolment records to show
> -- billing has been transfered to invoice table
> Update  tblUnitEnrolment
> SET      tblUnitEnrolment.BillingStatus=1
>
> FROM tblUnitEnrolment  INNER JOIN dbo.tblEnrolment ON
> dbo.tblUnitEnrolment.EnrolmentID = dbo.tblEnrolment.EnrolmentID
> WHERE tblUnitEnrolment.BillingStatus=0 and dbo.tblEnrolment.CourseID =
> @Course
>
> SET @Status=@InvoiceID
>
> -- Test for errors
> IF @@Error = 0
>        COMMIT
> ELSE
>        ROLLBACK
> GO
>

"hals_left" <cc900***@ntu.ac.uk> wrote in message
news:1124455212.454963.257740@g49g2000cwa.googlegroups.com...
> Thanks for the reply -
>
>>Did you check for error after each modification statement?
> Just at the end of the proc , I thought that any error would be
> visisble here. I gues this is where Im going wrong. Can you show us an
> example?
>
>>We can't comment on what happened until we see some code
> The full proc is in the post
>
>>I suggest you check out the error handling articles at www.sommarkog.se.
> The site seems to be down
>

"hals_left" <cc900***@ntu.ac.uk> wrote in message
news:1124455212.454963.257740@g49g2000cwa.googlegroups.com...
> Thanks for the reply -
>
>>Did you check for error after each modification statement?
> Just at the end of the proc , I thought that any error would be
> visisble here. I gues this is where Im going wrong. Can you show us an
> example?
>
>>We can't comment on what happened until we see some code
> The full proc is in the post
>
>>I suggest you check out the error handling articles at www.sommarkog.se.
> The site seems to be down
>