CODEWITHSUNDEEP
sql-servert-sql
abs786123
abs786123

Reputation: 609

IF statement in SQL Server around DBCC CHECKIDENT

I am trying to create an IF statement or maybe even a case but I can't seem to get the syntax right.

I have this:

DECLARE @var INT

SELECT @var = MAX(LeadAddressid)
FROM dbo.STG_LEAD_ADDRESS_BASE
WHERE LeadAddressid BETWEEN 600000000 AND 800000000

SET @var = @var + 1 

DBCC CHECKIDENT ('STG_LEAD_ADDRESS_BASE',RESEED, @var);

The condition I am trying to put is if the table is empty or no rows are returned I want the following code 

 DBCC CHECKIDENT ('STG_LEAD_ADDRESS_BASE',RESEED, 600000000);

If there is a value in the table between 600000000 AND 80000000

Then I want the following:

DBCC CHECKIDENT ('STG_LEAD_ADDRESS_BASE', RESEED, @var);

Can someone please advise how to do this in SQL Server? I have never used the IF statement in T-SQL - I am guessing that would be required?

Upvotes: 0

Views: 369

Answers (1)

Gouri Shankar Aechoor
Gouri Shankar Aechoor

Reputation: 1581

Try this. Hope it helps

IF EXISTS (
        SELECT TOP 1 *
        FROM dbo.STG_LEAD_ADDRESS_BASE
        WHERE LeadAddressid BETWEEN 600000000
                AND 800000000
        )
BEGIN
    DECLARE @var BIGINT


    SELECT @var = MAX(LeadAddressid)
    FROM dbo.STG_LEAD_ADDRESS_BASE
    WHERE LeadAddressid BETWEEN 600000000
            AND 800000000

    --SET @var = @var + 1

    DBCC CHECKIDENT (
            'STG_LEAD_ADDRESS_BASE',
            RESEED,
            @var
            );
END
ELSE
BEGIN
    DBCC CHECKIDENT (
            'STG_LEAD_ADDRESS_BASE',
            RESEED,
            600000000
            );
END

Test Script

Create Test Table

IF OBJECT_ID('Test_Table')IS NOT NULL
DROP TABLE Test_Table
GO

CREATE TABLE Test_Table (LeadAddressid INT IDENTITY(1, 1),SomeColumn VARCHAR(10))
GO

Insert Script

IF EXISTS (
        SELECT TOP 1 *
        FROM dbo.Test_Table
        WHERE LeadAddressid BETWEEN 600000000
                AND 800000000
        )
BEGIN
    DECLARE @var BIGINT


    SELECT @var = MAX(LeadAddressid)
    FROM dbo.Test_Table
    WHERE LeadAddressid BETWEEN 600000000
            AND 800000000

    SET @var = @var

    DBCC CHECKIDENT (
            'Test_Table',
            RESEED,
            @var
            );
END
ELSE
BEGIN
    DBCC CHECKIDENT (
            'Test_Table',
            RESEED,
            600000010
            );
END

INSERT INTO Test_Table
SELECT 'a'

SELECT * FROM Test_Table

First Run will Reseed the tables Identity to 600000000, Second run will reseed to 600000000 and the following insert will be 600000001, however if you were to inclement the variable, you would notice that the sequence would be 600000002 instead of 600000001. hope it helps.

Upvotes: 1

Related Questions