February 3, 2003 at 10:50 am
I am inserting records into a table where one of the fields (SeqNum) requires me to pick the next highest available number to populate that field:
Example of existing record
EmployeeID Date SeqNum
1 2/03/03 2
So when I insert the next record the SeqNum has to equal 3 (or the Sequence number plus one) How would I
1. design the Procedure that would give me that number and how do I
2. then pass it into the insert statment?
February 3, 2003 at 11:01 am
What I would do i save the highest number in a variable like this:
DECLARE @Nro AS INT
SELECT @Nro = TOP 1 SeqNum FROM Table Order BY SeqNum DESC
And then insert the value like this:
INSERT INTO Table
VALUES
(x,x,@Nro+1)
February 3, 2003 at 11:02 am
what I have done in a similar scenario is have a system table that stores the next available sequence number:
CREATE TABLE stblNextAvailableSeqNo
(
NextSeqNo INT NOT NULL
)
then, when doing inserts into another table, increment the next sequence number within the stored procedure, like this simple example:
CREATE PROCEDURE usp_SampleInsert
@New_EmpID INT
AS
DECLARE @NextSeqNo INT
SELECT @NextSeqNo = NextSeqNo FROM stblNextAvailableSeqNo
BEGIN TRANSACTION
INSERT INTO tblEmployee (EmployeeID, Date, SeqNum)
VALUES (@New_EmpID, GETDATE(), @NextSeqNo)
IF @@ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RETURN
END
UPDATE stblNextAvailableSeqNo
SET NextSeqNo = NextSeqNo + 1
IF @@ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
RETURN
Hope this helps...
Jay
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply