SQL Server – Reseed Identity in Stored Procedure

identityparametersql serverstored-procedures

I have a bunch of LU tables that have values added and deleted all the time. Therefore, I figured writing a quick stored procedure would serve me well and then can call the stored procedure on another stored procedure that deletes values from the LU tables and then reseeds the ID column directly after.

Stored procedure code

Create Procedure DBA.ReseedIdentity

@TableName nvarchar(20) = NULL,
@IdentityColumn nvarchar(25) = NULL

AS BEGIN 
SET NOCOUNT ON 
SET XACT_ABORT ON 

    
IF (@TableName is null or @IdentityColumn is null)
    BEGIN
        RAISERROR('You Must Specify a Table AND the ID Column from that Table to Reseed the Identity!', 16,1)
        RETURN;
    END 

Declare @MaxID int
set @MaxID = (SELECT ISNULL(MAX(@IdentityColumn),0) FROM @TableName)
DBCC CHECKIDENT(@TableName, RESEED, @MaxID)


END

I wanted to know if there is any trick to be able to pass the @TableName parameter following the FROM clause without dynamically creating it. I am new to writing stored procedures, so please forgive me if the answer is obvious.

Any help is greatly appreciated.

Best Answer

If you are completely deleting all the values from the lookup table, have you considered just truncating them? That would save on TLOG activity and automatically reseed the values.
You can't substitute parameters like that in queries, at least not for object names. You would need to do this dynamically.

Please see an example for you below. I beefed up the sanity check for you for edge cases you may not have considered... hopefully the below is clear.

CREATE PROCEDURE DBA.ReseedIdentity
(
    @TableName SYSNAME
    , @IdentityColumn SYSNAME
)
AS 
BEGIN

/** Sanity Checking **/
IF @TableName IS NULL OR @IdentityColumn IS NULL
BEGIN
    ;THROW 50000, 'Must specify table name and Identity column name.', 1;
END

IF OBJECT_ID(@TableName) IS NULL
BEGIN
    ;THROW 50000, 'Tablename does not exist.', 1;
END

IF NOT EXISTS (SELECT TOP (1) 1 FROM sys.columns AS C WHERE C.name = @IdentityColumn AND C.object_id = OBJECT_ID(@TableName))
BEGIN
    ;THROW 50000, 'Identity column is not found on specified table.', 1;
END

/** Reset Identity
    Assumes Identity column is an INT
    **/
DECLARE @SQLCommand NVARCHAR(4000)

SET @SQLCommand = N'
    DECLARE @MaxID INT;
    SET @MaxID = COALESCE((SELECT MAX(' + QUOTENAME(@IdentityColumn) + ') FROM ' + QUOTENAME(@TableName) + '), 1);

    DBCC CHECKIDENT(' + QUOTENAME(@TableName, '''') + ', RESEED, @MaxID);
    ';

EXEC sp_executesql @SQLCommand;

END

Related Solutions

SQL Server – Insert/Update Stored Procedure

One execution of your script will produce one computer name and a list of one or more monitors. You can pass these to a SQL Server stored procedure, you just have to get the parameter types correct.

The computer name is simplest. With just this the SP definition would look like this:

create procedure dbo.MyProcedureName
    @ComputerName varchar(100)
as
    ...

I use varchar(100) as an example. You use whatever's right for you. The list of monitors is more difficult because it may contain many values. For this you have to use a table-valued parameter and for that you will have to define a type:

create type MonitorList AS TABLE (
    MonitorName varchar(100)
    );
GO

Now the SP looks like this:

create procedure dbo.MyProcedureName
    @ComputerName varchar(100),
    @Monitors     MonitorList    READONLY
as
    ...

Now to the body of the SP. If you use the names as the primary key in tables Computer and Monitor the INSERT statements are straightforward. Assuming you have primary keys and foreign keys defined -- you should -- you have to insert in the correct sequence to respect these key definitions i.e. into Computer and Monitor and only then into the mapping table.

insert dbo.Computer (ComputerName)
values (@ComputerName);

The type we defined, and variables created off it, behave just like any other table:

insert dbo.Monitor (MonitorName)
select MonitorName
from @Monitors;

insert Monitor_Computer_Map (ComputerName, MonitorName)
select @ComputerName, MonitorName
from @Monitors;

If your tables have surrogate keys that use IDENTITY, however, you have more work to do. You have to capture these identity values as they are generated in the parent tables and use them in the mapping table. This can be done with the OUPUT clause:

declare @ComputerID table ( ComputerID int);

insert dbo.Computer (ComputerName)
output INSERTED.ID
into @ComputerID
values (@ComputerName);

Do the same thing for monitors and use the local table variables to populate the mapping table.

Of course you want to have appropriate validation, duplicate checking and error handling in the body of the SP, too.

You don't say what scripting language you use. The documentation for it will tell you how to declare and populate stored procedure parameters for SQL Server.

Response to OP's EDIT #2:

First, a few tips. Please post the full error message; it helps immensely with debugging. Second, if you're using SSMS you can double-click an error and it will highlight the code in error. Third, get in the habit of closing your statements with a semicolon. It is not required yet but it will be soon.

If all computers have exactly one monitor then the TVP is not needed. You are correct. How many developers have only one monitor these days? I've seen finance traders' stations with eight. In these cases you do want a TVP. Please, please, please do not be tempted to write @Serial1, @Serial2, @Serial3 ....

Your code will throw the error Must declare the scalar variable "@MonitorId". This is because of your third INSERT statement:

INSERT INTO dbo.Monitor_Computers (Monitor_Computer_Monitor, Monitor_Computer_Computer)
VALUES (@MonitorId, @ComputerId);

When you use the INSERT..VALUES syntax SQL Server demands that there can be only one value per variable. You have provided table valued variables which could (potentially) hold many values. What you need is the INSERT..SELECT syntax. Yes, I know @ComputerID only has one row but it is a table and it could have many rows. What you need is:

INSERT INTO dbo.Monitor_Computers (Monitor_Computer_Monitor, Monitor_Computer_Computer)
SELECT m.Id, c.Id
from @ComputerId AS c
cross join @MonitorId AS m;

Debugging Stored Procedure in Visual Studio – Object Reference Not Set Error

Do you have DELIMITER statements around the CREATE...?

Does VS have any way to debug Stored Routines? (I suspect not.)

The failure to do it this way could cause trouble later:

START TRANSACTION;
SELECT ... from users ... FOR UPDATE;
various SETs
UPDATE ...;
COMMIT;

Another consideration... An "audit trail" may be better achieved via a TRIGGER wherein you can compare NEW. to OLD..

Stored procedure code

Best Answer

Related Solutions

SQL Server – Insert/Update Stored Procedure

Debugging Stored Procedure in Visual Studio – Object Reference Not Set Error

Related Question