How to increment number in T-SQL (not for auto-incremented key)?

Question

I have this table

CREATE TABLE Dept 
(
    Deptno INT NOT NULL,
    Dname VARCHAR(50)  NOT NULL,
    Loc VARCHAR(50)  NOT NULL,

    CONSTRAINT Dept_PK PRIMARY KEY(Deptno)
);

As you see Deptno is not autoincremented.

But I need to create a procedure that inserts Dname and Loc, where Deptno will be auto-generated.

What I have so far:

CREATE PROCEDURE InserNewDepartmentWithoutNumber
    @name VARCHAR(50),
    @location VARCHAR(50)
AS
    IF EXISTS(SELECT * FROM Dept WHERE Dept.Dname = @name)
        PRINT 'This department already exists'
    ELSE
    BEGIN
        INSERT INTO Dept (Dname, Loc)
        VALUES (@name, @location)       
    END

@stickybit It is completed database, i think i am not allowed to change created table. — andrew17, Dec 14 '19 at 18:02
Perhaps a [`sequence`](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15) would be of use? — HABO, Dec 14 '19 at 18:03
[This](https://stackoverflow.com/questions/14413383/sequence-as-default-value-for-a-column) question and its answers should get you on the right path. — HABO, Dec 14 '19 at 19:17

robbpriestley · Answer 1 · 2019-12-15T19:50:08.237

0

Just look up the last, highest, used Deptno and add one to it. But, we do need to be careful to ensure that if this procedure is called more than once simultaneously, that we do not issue the same new Deptno. To protect against that, we use a transaction, and temporarily hold a lock on the Dept table.

CREATE PROCEDURE InserNewDepartmentWithoutNumber
    @name VARCHAR(50),
    @location VARCHAR(50)
AS
BEGIN
    BEGIN TRANSACTION
        IF EXISTS(SELECT * FROM Dept WITH (UPDLOCK, HOLDLOCK) WHERE Dept.Dname = @name)
            PRINT 'This department already exists'
        ELSE
        BEGIN
            DECLARE @deptno INT
            SET @deptno = (SELECT MAX(Deptno) + 1 FROM Dept WITH (UPDLOCK, HOLDLOCK))
            INSERT INTO Dept (Deptno, Dname, Loc) VALUES (@deptno, @name, @location)    
        END
    COMMIT
END

edited Dec 15 '19 at 19:50

answered Dec 14 '19 at 20:45

robbpriestley

3,050
2
24
36

1

Since you don't use an explicit transaction you provide an opportunity for multiple users to add the same number if they run the SP at the same time. If a department can be deleted then you will reissue a department number if the deleted department had the maximum `DeptNo` value. – HABO Dec 14 '19 at 22:02
True. I've added the transaction. The problem with the re-issued `DeptNo` may or may not be a problem for business, but for the sake of simplicity, and unless otherwise stated, I will assume not. – robbpriestley Dec 14 '19 at 23:54
1

You'll also need to add `TabLockX` and `HoldLock` [table hints](https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15) to the `select`, otherwise there is still a race condition since a `select` would not hold a lock for the duration of the transaction. – HABO Dec 15 '19 at 03:19
... Or simply [set transaction isolation level to serializable](https://stackoverflow.com/questions/52780121/using-a-if-condition-in-an-insert-sql-server/52780490#comment92527492_52780121)... – Zohar Peled Dec 15 '19 at 06:29
1

@ZoharPeled while SERIALIZABLE will prevent the duplicate insert, it will do so by producing a deadlock, which is kind of unpleasant. – David Browne - Microsoft Dec 15 '19 at 15:57
1

@HABO TABLOCKX is excessive (UPDLOCK,HOLDLOCK) is sufficient, and won't lock the whole table in the normal case where there's an index. – David Browne - Microsoft Dec 15 '19 at 15:58
Changed hint to UPDLOCK, HOLDLOCK – robbpriestley Dec 15 '19 at 16:54
The EXISTS query needs the lock hint too, and needs to be inside the transaction. – David Browne - Microsoft Dec 15 '19 at 17:19
@DavidBrowne-Microsoft I had contemplated `UpdLock`, but dismissed it because a different row is being added. My bad. It will prevent another session from getting the `max` value if it executes this SP. Thanks for pointing it out. – HABO Dec 15 '19 at 19:43

How to increment number in T-SQL (not for auto-incremented key)?

1 Answers1