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在数据库中不存在!