CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresdbcc
Ajay Dwivedi
Ajay Dwivedi

Reputation: 328

DBCC Command Not Working Inside Procedure

I have below query. Logically, the procedure usp_mytran should RESEED the Identity to 1 for table dbo.Sales. But the last query is returning different values for Max_ID_Value and Current_Seed_Value. Can anyone please explain why DBCC command is not working inside procedure?

USE tempdb

--  Create table 
CREATE  TABLE dbo.Sales
(ID INT IDENTITY(1,1), Address VARCHAR(200))
GO

--  Procedure to Populate data into dbo.Sales
CREATE PROCEDURE usp_mytran 
AS
BEGIN
BEGIN TRANSACTION
    INSERT dbo.Sales
            ( Address )
    VALUES  ( 'Dwarka, Delhi' );

    --  Delete it for some reason
    DELETE FROM dbo.Sales;

    --  Code to check max ID value, and verify it again IDENTITY SEED
    DECLARE @MaxValue INT = (SELECT ISNULL(MAX(ID),1) FROM dbo.Sales)
    IF @MaxValue IS NOT NULL AND @MaxValue <> IDENT_CURRENT('dbo.Sales')
        DBCC CHECKIDENT ( 'tempdb.dbo.Sales', RESEED, @MaxValue );
ROLLBACK TRANSACTION
END

--  Ideally, this should RESEED the Identity of dbo.Sales table.
EXEC usp_mytran

--  Max_ID_Value & Current_Seed_Value should be same
SELECT ISNULL(MAX(ID),1) AS Max_ID_Value, IDENT_CURRENT('dbo.Sales') AS Current_Seed_Value FROM dbo.Sales

Upvotes: 2

Views: 1659

Answers (2)

Ajay Dwivedi
Ajay Dwivedi

Reputation: 328

Sorry for answering my own question. As pointed by @Kannan Kandasamy, it is the ROLLBACK TRANSACTION code that is reverting back the work done by DBCC CHECKIDENT. So to make it work, I have created a job with name Reseed_Sales containing code to RESEED Identity for table dbo.Sales. Below is the final query for procedure usp_mytran.

--  Procedure to Populate data into dbo.Sales
ALTER PROCEDURE usp_mytran 
AS
BEGIN
BEGIN TRANSACTION
    INSERT dbo.Sales
            ( Address )
    VALUES  ( 'Dwarka, Delhi' );

    --  Delete it for some reason
    DELETE FROM dbo.Sales;

    --  Code to check max ID value, and verify it again IDENTITY SEED
    DECLARE @MaxValue INT = (SELECT ISNULL(MAX(ID),1) FROM dbo.Sales)
    IF @MaxValue IS NOT NULL AND @MaxValue <> IDENT_CURRENT('dbo.Sales')
        EXEC msdb..sp_start_job @job_name = 'Reseed_Sales'
ROLLBACK TRANSACTION
END

Upvotes: 3

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

Actually it is working as expected inside stored procedure: Rollback tran will rollback the checkident value - that is what happening in the code

--  Procedure to Populate data into dbo.Sales
alter PROCEDURE usp_mytran 
AS
BEGIN
    BEGIN TRANSACTION
    INSERT dbo.Sales
            ( Address )
    VALUES  ( 'Dwarka, Delhi' );

    --  Delete it for some reason
    DELETE FROM dbo.Sales;

    --  Code to check max ID value, and verify it again IDENTITY SEED
    DECLARE @MaxValue INT = (SELECT ISNULL(MAX(ID),1) FROM dbo.Sales)
    IF @MaxValue IS NOT NULL AND @MaxValue <> IDENT_CURRENT('test.dbo.Sales')
        DBCC CHECKIDENT ( 'test.dbo.Sales', RESEED, @MaxValue );
    ROLLBACK TRANSACTION
END

If you check ident_current now it shows 2 and on the next run it shows 4 etc because of ROLLBACK TRANSACTION

Now check after multiple executions "EXEC usp_mytran"

select IDENT_CURRENT('test.dbo.Sales')

You will see that checkident will not reset.

If we remove that transaction then CHECKIDENT will reseed to 1.

Commented transaction below

--  Procedure to Populate data into dbo.Sales
alter PROCEDURE usp_mytran 
AS
BEGIN
    --BEGIN TRANSACTION
    INSERT dbo.Sales
            ( Address )
    VALUES  ( 'Dwarka, Delhi' );

    --  Delete it for some reason
    DELETE FROM dbo.Sales;

    --  Code to check max ID value, and verify it again IDENTITY SEED
    DECLARE @MaxValue INT = (SELECT ISNULL(MAX(ID),1) FROM dbo.Sales)
    IF @MaxValue IS NOT NULL AND @MaxValue <> IDENT_CURRENT('test.dbo.Sales')
        DBCC CHECKIDENT ( 'test.dbo.Sales', RESEED, @MaxValue );
    --ROLLBACK TRANSACTION
END

Now check after multiple executions "EXEC usp_mytran"

select IDENT_CURRENT('test.dbo.Sales')

You will see the reseed value as '1'

Check for below example as well...

Can we rollback to original state after we have used DBCC CHECKIDENT to restart Identity column count?

Upvotes: 1

Related Questions