CODEWITHSUNDEEP
c#sqlsql-serverstored-procedures
SummerDays
SummerDays

Reputation: 57

Store a number and increase by one with stored procedure

I am trying to store Code and increment by one with a new form submission (payment form with Authorize.Net). However I am getting back some strange numbers. For instance, there is one point it goes from 66 to 55 to 73!

NULL, NULL, 84, 83, 83, 82, 82, NULL, 81, 80, NULL, 80, 80, NULL, NULL... 75, 74, 73, 55, 66, 72, NULL, 71, 71, 70...

I am thinking my Stored Procedure needs changed, but I am looking for some advice because I do not understand why this is even happening.

Upvotes: 0

Views: 121

Answers (1)

Ross Bush
Ross Bush

Reputation: 15185

You should let the database manage the identity column because it is more capable of getting that right than you application(s). Simply add a new IDENTITY column to your Payment table. This will also allow you to easily identify multiple payments against one invoice.

ALTER TABLE Payment ADD PaymentID INT IDENTITY(1,1) NOT NULL

//This does not change
INSERT INTO Payment (EventCode, EventInstance, EventCount, InvoiceNumber)

//MAX Payment for invoice 
SELECT PaymentID=MAX(PaymentID)  FROM Payment 
  WHERE (EventCode = @EventCode AND
        EventInstance = @EventInstance)

//OR Since you just inserted on the same connection simply use @IDENTITY
SELECT PaymentID=@IDENTITY

Upvotes: 3

Related Questions