如何为SQL中的每个SQL执行语句设置转到错误处理程序并回滚？

@TableName = TableName， @ColumnName = ColumnName， @DataType = DataType 来自@Identity_Details的 其中IDVal = @Loop - 每个身份插入一个用_dup后缀创建的新列（前导字符串） 选择@NewColu mn_Creation_Query ='ALTER TABLE'+ @SchemaName +'。'+ @TableName +'ADD'+ @ ColumnName + @Leadingstring +''+ @DataType exec sp_executesql @NewColumn_Creation_Query 打印'新列创建成功完成' - 结束 - 每个身份将新创建的临时列中的数据（_dup）更新为实列 选择@UpdateColumn_Query ='UPDATE'+ @SchemaName +'。'+@TableName +'SET'+ @ColumnName + @ Leadingstring +' ='+ @ColumnName exec sp_executesql @UpdateColumn_Query 打印'使用现有值更新的新列值' - 结束 --- exec Drop_FK @TableName - 删除新创建的表的外键构词 insert @Foreign_keys（ForeignKeyName，ParentTable） select name，object_name（[parent_object_id]） 其中object_name（[referenced_object_id]）= @TableName $ / b 选择 @Count_FK = max（IDVal）， @Loop_FK = MIN（IdVal） 来自 @Foreign_keys while（@ Loop_FK< = @ Count_FK） 开始 选择@Drop_Key_Query ='ALTER TABLE'+ ParentTable +'DROP CONSTRAINT'+ ForeignKeyName 来自@Foreign_keys的其中Idval = @Loop_FK - 选择@Drop_Key_Query exec sp_executesql @Drop_Key_Query select @Drop_Key_Query ='' select @Loop_FK = @Loop_FK + 1 结束 打印'所有外国钥匙造物商掉落' - -End --- exec DropAllColumnConstraints @ TableName，@ ColumnName --Drop All Column Constraints 而0 = 0开始 声明@constraintName varchar（128） set @constraintName =（ 从info_schema.constraint_column_usage中选择top 1 constraint_name 其中table_name = @tableName和column_name = @columnName） 如果@constraintName为null break --print（'alter table' + @ tableName +'drop constraint'+ @ constraintName +''） exec（'alter table'+ @ TableName +'drop constraint'+ @ constraintName +''） 结束 打印'主要关键字Constriants掉落' - 结束 - 已经存在的列已经丢失了。 选择@DropColumn_Query ='ALTER TABLE'+ @SchemaName +'。'+ @ TableName +'DROP COLUMN'+ @ColumnName --PRINT @DropColumn_Query exec sp_executesql @DropColumn_Query 打印'已经存在的列已经丢失' - 结束 - 在列之前已经存在列的with_dup列的列号已经存在 选择@Column_Rename_Query ='exec sp_rename'+''''+ @SchemaName +'。'+ @TableName +'。'+ @ ColumnName + @Leadingstring +''''+'，'+''''+ @ColumnName +''''+'，'+''''+'COLUMN'+'''' - 打印@Column_Rename_Query exec sp_executesql @Column_Rename_Query 打印'列名从临时名称重命名为原始列名' - 结束 - 将New Column重命名为not null select @NewColumn_Creation_Query =' ALTER TABLE'+ @SchemaName +'。'+ @TableName +'ALTER COLUMN'+ @ ColumnName +''+ @DataType +'Not Null' --PRINT @NewColumn_Creation_Query exec sp_executesql @NewColumn_Creation_Query 打印'新列名数据类型设置为不可为空' - 结束 - 为标识插入列添加主键约束 选择@Primary_Key_Creation_Query ='ALTER TABLE'+ @SchemaName +'。'+ @TableName +'添加约束'+'PK_'+ @TableName + +'_'+ @ ColumnName +'主键（'+'['+ @ ColumnName +']'+'）' - 打印@Primary_Key_Creation_Query exec sp_executesql @Primary_Key_Creation_Query 打印'为主键列添加的主键约束' - 结束 选择 @Loop = @Loop + 1， @NewColumn_Creation_Query =''， @UpdateCo lumn_Query =''， @Column_Rename_Query =''， @Primary_Key_Creation_Query =''， @DropColumn_Query ='' 从@Foreign_keys删除 结束 COMMIT TRAN What I have tried:Script I am using:/****** Object: StoredProcedure [dbo].[Remove_Identity_Columns] Script Date: 04/11/2016 18:32:54 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--exec CHK_INST_IDENTFIER '400220101','','' --exec Remove_Identity_Columns ALTER proc [dbo].[Remove_Identity_Columns]as--Create a Temporary Table @Identity_Details to which Identity Value,Schema Name,Table/Column Name/Data Type from Identity Insert Tables Data is Passeddeclare @Identity_Details table(IDVal int identity(1,1) ,SchemaName varchar(25),TableName varchar(25),ColumnName varchar(25),DataType varchar(10))declare @Foreign_keys table(Idval int identity(1,1),ForeignKeyName varchar(1000),ParentTable varchar(100))declare @NewColumn_Creation_Query nvarchar(1000),@UpdateColumn_Query nvarchar(1000),@Column_Rename_Query nvarchar(1000),@Primary_Key_Creation_Query nvarchar(1000),@DropColumn_Query nvarchar(1000),@Leadingstring varchar(5),@SchemaName varchar(25),@TableName varchar(25),@ColumnName varchar(25),@DataType varchar(10),@Count int,@Loop int,@Count_FK int,@Loop_FK int,@Drop_Key_Query nvarchar(1000)BEGIN TRANinsert @Identity_Details(SchemaName,TableName,ColumnName,DataType) -- Schema/Table Name/Column Name/Data Type etc with Identity Insert Columns passed to Temporary Table @Identity_Details select c.TABLE_SCHEMA ,t.TABLE_NAME ,c.COLUMN_NAME, c.DATA_TYPE from information_schema.columns cinner join information_schema.tables t on t.TABLE_NAME = c.TABLE_NAMEwherecolumnproperty(object_id(c.TABLE_NAME),c.COLUMN_NAME, 'IsIdentity') = 1 --End -- Column Names with _dup suffixed and count etc passed to Temporary Table @Identity_Detailsselect @Leadingstring= '_dup',@Count =count(*),@Loop = 1,@NewColumn_Creation_Query = '',@UpdateColumn_Query='',@DropColumn_Query='',@Column_Rename_Query='',@Primary_Key_Creation_Query=''from@Identity_Details --End--Passing the Identity Insert Tables one by one from Temporary Table with Table/Column/Schema Names of the same one by one in the While Loopwhile(@Loop<=@Count)beginselect@SchemaName = SchemaName,@TableName = TableName,@ColumnName = ColumnName,@DataType = DataTypefrom @Identity_Detailswhere IDVal = @Loop --To each identity insert a new column created with _dup suffixed(Leading String)select @NewColumn_Creation_Query = 'ALTER TABLE ' + @SchemaName + '.'+ @TableName + ' ADD ' +@ColumnName + @Leadingstring + ' ' + @DataType exec sp_executesql @NewColumn_Creation_Query Print 'New Column Creation Completed Successfully' --End --To each identity update data in the Temp Column newly created (_dup) to Real Columnselect @UpdateColumn_Query = 'UPDATE ' + @SchemaName + '.'+@TableName + ' SET ' + @ColumnName + @Leadingstring+ ' = ' + @ColumnName exec sp_executesql @UpdateColumn_Query Print 'New Column Values Updated with Existing Values'--End ---exec Drop_FK @TableName--Drop the Foreign key Constriants of Newly Created Tablesinsert @Foreign_keys (ForeignKeyName,ParentTable)select name,object_name([parent_object_id])from sys.foreign_keys where object_name([referenced_object_id]) = @TableNameselect@Count_FK = max(IDVal),@Loop_FK = MIN(IdVal) from@Foreign_keyswhile(@Loop_FK<=@Count_FK)beginselect @Drop_Key_Query = 'ALTER TABLE ' + ParentTable + ' DROP CONSTRAINT ' + ForeignKeyNamefrom @Foreign_keys where Idval = @Loop_FK --select @Drop_Key_Queryexec sp_executesql @Drop_Key_Queryselect @Drop_Key_Query = ''select @Loop_FK = @Loop_FK + 1end Print 'All the Foreign key Constriants Dropped' --End---exec DropAllColumnConstraints @TableName,@ColumnName--Drop All Column Constraintswhile 0=0 begin declare @constraintName varchar(128) set @constraintName = ( select top 1 constraint_name from information_schema.constraint_column_usage where table_name = @tableName and column_name = @columnName ) if @constraintName is null break --print ('alter table "'+@tableName+'" drop constraint "'+@constraintName+'"') exec ('alter table "'+@TableName+'" drop constraint "'+@constraintName+'"') endPrint 'Primary key Constriants Dropped'--End --Already Existing Columns dropped afterwards. select @DropColumn_Query = 'ALTER TABLE '+ @SchemaName + '.'+@TableName + ' DROP COLUMN ' + @ColumnName--PRINT @DropColumn_Queryexec sp_executesql @DropColumn_Query Print 'Already Exisiting Columns Dropped'--End --Rename Columns with_dup suffixed to Column already existed before select @Column_Rename_Query = 'exec sp_rename ' + '''' + @SchemaName + '.' + @TableName + '.' +@ColumnName + @Leadingstring +'''' + ',' + '''' + @ColumnName + '''' + ',' + '''' +'COLUMN' +''''-- Print @Column_Rename_Queryexec sp_executesql @Column_Rename_Query Print 'Column Name Renamed from Temporary to Original Existing Column Names'--End --Set the New Column renamed as not null select @NewColumn_Creation_Query = 'ALTER TABLE ' + @SchemaName + '.'+ @TableName + ' ALTER COLUMN ' +@ColumnName + ' ' + @DataType + ' Not Null'--PRINT @NewColumn_Creation_Queryexec sp_executesql @NewColumn_Creation_QueryPrint 'New Column Name Data Type set as Not Nullable' --End --Primary Key Constraint Added for Identity Insert Columnsselect @Primary_Key_Creation_Query = 'ALTER TABLE ' + @SchemaName + '.' + @TableName + ' add constraint ' + 'PK_' + @TableName + +'_' + @ColumnName+' primary key(' + '['+@ColumnName+']' + ')'--Print @Primary_Key_Creation_Queryexec sp_executesql @Primary_Key_Creation_Query Print 'Primary Key Constraint Added for Primary Key Columns'--Endselect @Loop = @Loop + 1,@NewColumn_Creation_Query = '',@UpdateColumn_Query = '',@Column_Rename_Query = '',@Primary_Key_Creation_Query = '',@DropColumn_Query = ''delete from @Foreign_keysendCOMMIT TRAN推荐答案 对于我所知道的开始交易，如果您更深入地使用它，对于您想要的每个流程，您可以按顺序为每个交易分配一个标识符知道崩溃和回滚到你想要的交易的位置。 看看这里 BEGIN TRANSACTION（Transact-SQL） [ ^ ] 此处为回滚事务ROLLBACK TRANSACTION（Transact-SQL） [ ^ ] 希望这有助于For what i know about "Begin transaction" if you use it more deeply, for each process you want, you can assign an identifier to each transaction in order to know where it crashes and rollback to the transaction you want.Take a look here BEGIN TRANSACTION (Transact-SQL)[^]And here for the rollback transactions ROLLBACK TRANSACTION (Transact-SQL)[^]Hope this helps 这篇关于如何为SQL中的每个SQL执行语句设置转到错误处理程序并回滚？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！