CREATE PROCEDURE InsertPaymentArrangements
	
	@DebtorID int,
	@FirstPaymentDate datetime,
	@LastPaymentDate datetime,
	@NextPaymentDate datetime,
	@NextReviewDate datetime,
	@DayOfMonth int,
	@ArrangementDate datetime,
	@CompletionDate datetime,
	@AmountInWords varchar(60),
	@AmountInFigures money,
	@PaymentTypeID int,
	@NumberOfPayments int,
	@ExpiryDate datetime,
	@ArrangementComplete bit,
	@PaymentCategory int,
	@APR float,
	@PaymentFrequency int,
	@CreatedBy int,
	@CreatedOn datetime,
	@TimeStamp timestamp OUTPUT	

AS

INSERT INTO [PaymentArrangements] (
	DebtorID, FirstPaymentDate,	LastPaymentDate, NextPaymentDate,
	NextReviewDate,	DayOfMonth,	ArrangementDate, CompletionDate, AmountInWords,
	AmountInFigures, PaymentTypeID, NumberOfPayments, CreatedBy,
	CreatedOn, ExpiryDate, ArrangementComplete, PaymentCategory, APR
	)
	
VALUES(
	
	@DebtorID, @FirstPaymentDate, @LastPaymentDate, @NextPaymentDate,
	@NextReviewDate, @DayOfMonth, @ArrangementDate, @CompletionDate, @AmountInWords,
	@AmountInFigures, @PaymentTypeID, @NumberOfPayments, @CreatedBy,
	@CreatedOn, @ExpiryDate, @ArrangementComplete, @PaymentCategory, @APR
)	

IF @@ROWCOUNT > 0
BEGIN
	SELECT @TimeStamp = [TimeStamp]
	FROM PaymentArrangements
	WHERE PaymentArrangementID = SCOPE_IDENTITY()
END

GO

-- update Debtor table for report/letter merge references ------

UPDATE Debtor

SET 

	NoOfPaymentsDue = @NumberOfPayments,
	PaymentReviewDate = @PaymentReviewDate,
	FirstPaymentDate = @FirstPaymentDate,
	DayOfMonth = @DayOfMonth,
	PaymentTypeID = @PaymentTypeID,
	AmountInWords = @AmountInWords,
	AmountInFigures = @AmountInFigures

WHERE
	DebtorID = @DebtorID AND
	[TimeStamp] = @TimeStamp


IF @@ROWCOUNT > 0
BEGIN
	SELECT @TimeStamp = [TimeStamp]
	FROM Debtor
	WHERE DebtorID = @DebtorID
END

quote:

---

Originally posted by webfred

you cannot use variable when a GO ends the batch.
delete that GO and try.

---

No, you're never too old to Yak'n'Roll if you're too young to die.

---