sql server 修改表自增列的值

Create PROCEDURE [dbo].[SP_UpdateIdentityId]
    (
      @tableName NVARCHAR(100) ,
      @beforeId INT ,
      @afterId INT
    )
AS
    BEGIN
        IF @beforeId IS NULL
            OR @afterId IS NULL
            OR @tableName IS NULL
            BEGIN
                PRINT 'param is null'
                RETURN
            END
    
        DECLARE @tb_id INT= OBJECT_ID(@tableName)
        IF @tb_id IS NULL
            BEGIN
                PRINT 'table not exist'
                RETURN
            END

        DECLARE @identityId NVARCHAR(200)

        SET @identityId = ( SELECT  name
                            FROM    sys.columns
                            WHERE   object_id = @tb_id
                                    AND is_identity = 1
                          )

        IF @identityId IS NULL
            BEGIN
                PRINT 'table not exist identity column'
                RETURN
            END

        DECLARE @columns NVARCHAR(MAX)
        SET @columns = ( SELECT ',' + name
                         FROM   sys.columns
                         WHERE  object_id = @tb_id
                                AND is_identity = 0
                       FOR
                         XML PATH('')
                       )
        SET @columns = STUFF(@columns, 1, 1, '')

        --PRINT @columns

        DECLARE @sql NVARCHAR(MAX)

        SET @sql = 'SELECT  *
INTO    #tmp_update_identity
FROM    ' + @tableName + '
WHERE   ' + @identityId + ' = @beforeId

if not exists(select 1 from #tmp_update_identity)
    begin
        print ''beforeId row data not exist''
        return
    end

        if exists(select 1 from  ' + @tableName + ' WHERE   ' + @identityId
            + ' = @afterId)
    begin
        print ''afterId row data already exist''
        return
    end

ALTER TABLE #tmp_update_identity DROP COLUMN ' + @identityId + '

begin try
 BEGIN TRANSACTION TRANSACTION_SP_UpdateIdentityId;
DELETE  FROM  ' + @tableName + ' WHERE   ' + @identityId + ' = @beforeId

SET IDENTITY_INSERT ' + @tableName + ' ON

INSERT  ' + @tableName + '(' + @identityId + ' ,' + @columns + ')
        SELECT  @afterId,*   FROM    #tmp_update_identity
        
        DROP TABLE #tmp_update_identity
        print ''ok'' 
        select 1 [state]
        COMMIT TRANSACTION 
end try
begin catch
print '' try catch ROLLBACK  TRANSACTION''
ROLLBACK  TRANSACTION
end catch
        '
        --PRINT @sql

        IF EXISTS ( SELECT  *
                    FROM    tempdb..sysobjects
                    WHERE   id = OBJECT_ID('tempdb..#tmp_update_identity') )
            DROP TABLE #tmp_update_identity

        EXEC sys.sp_executesql @sql,
            N'@tableName NVARCHAR(100) , @beforeId INT ,  @afterId INT',
            @tableName, @beforeId, @afterId

        IF EXISTS ( SELECT  *
                    FROM    tempdb..sysobjects
                    WHERE   id = OBJECT_ID('tempdb..#tmp_update_identity') )
            DROP TABLE #tmp_update_identity

    END

 

默认限制修改后的Id在数据库中不存在！

转载于:https://www.cnblogs.com/lizhanglong/p/5148474.html