如何为SQL中的每个SQL执行语句设置转到错误处理程序并回滚？ [英] How to set go to error handler and roll back for each SQL execution statements in SQL?
问题描述
How to set go to error handler and roll back for each sql execution statements in Sql?
<pre lang="SQL">/****** Object: StoredProcedure [dbo].[Remove_Identity_Columns] Script Date: 04/11/2016 18:32:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--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 Passed
declare @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 TRAN
insert @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 c
inner join
information_schema.tables t
on t.TABLE_NAME = c.TABLE_NAME
where
columnproperty(object_id(c.TABLE_NAME),c.COLUMN_NAME, 'IsIdentity') = 1
--End
-- Column Names with _dup suffixed and count etc passed to Temporary Table @Identity_Details
select
@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 Loop
while(@Loop<=@Count)
begin
select
@SchemaName = SchemaName,
@TableName = TableName,
@ColumnName = ColumnName,
@DataType = DataType
from @Identity_Details
where 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 Column
select @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 Tables
insert @Foreign_keys (ForeignKeyName,ParentTable)
select name,object_name([parent_object_id])
from sys.foreign_keys
where object_name([referenced_object_id]) = @TableName
select
@Count_FK = max(IDVal),
@Loop_FK = MIN(IdVal)
from
@Foreign_keys
while(@Loop_FK<=@Count_FK)
begin
select @Drop_Key_Query = 'ALTER TABLE ' + ParentTable + ' DROP CONSTRAINT ' + ForeignKeyName
from @Foreign_keys where Idval = @Loop_FK
--select @Drop_Key_Query
exec sp_executesql @Drop_Key_Query
select @Drop_Key_Query = ''
select @Loop_FK = @Loop_FK + 1
end
Print 'All the Foreign key Constriants Dropped'
--End
---exec DropAllColumnConstraints @TableName,@ColumnName
--Drop All Column Constraints
while 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+'"')
end
Print 'Primary key Constriants Dropped'
--End
--Already Existing Columns dropped afterwards.
select @DropColumn_Query = 'ALTER TABLE '+ @SchemaName + '.'+@TableName + ' DROP COLUMN ' + @ColumnName
--PRINT @DropColumn_Query
exec 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_Query
exec 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_Query
exec sp_executesql @NewColumn_Creation_Query
Print 'New Column Name Data Type set as Not Nullable'
--End
--Primary Key Constraint Added for Identity Insert Columns
select @Primary_Key_Creation_Query = 'ALTER TABLE ' + @SchemaName + '.' + @TableName + ' add constraint ' + 'PK_' + @TableName + +'_' + @ColumnName+' primary key(' + '['+@ColumnName+']' + ')'
--Print @Primary_Key_Creation_Query
exec sp_executesql @Primary_Key_Creation_Query
Print 'Primary Key Constraint Added for Primary Key Columns'
--End
select
@Loop = @Loop + 1,
@NewColumn_Creation_Query = '',
@UpdateColumn_Query = '',
@Column_Rename_Query = '',
@Primary_Key_Creation_Query = '',
@DropColumn_Query = ''
delete from @Foreign_keys
end
COMMIT TRAN</pre>
我的尝试：
脚本我正在使用：
/ ******对象：StoredProcedure [dbo]。[Remove_Identity_Columns]脚本日期：04/11 / 2016 18:32:54 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec CHK_INST_IDENTFIER'400220101'，''，''
- -exec Remove_Identity_Columns
ALTER proc [dbo]。[Remove_Identity_Columns]
as
- 创建一个临时的表@Identity_Details通过标识插入表数据传递的标识值，模式名称，表/列名称/数据类型
声明@Id entity_Details表
（
IDVal int identity（1,1），
SchemaName varchar（25），
TableName varchar（25），
ColumnName varchar（25），
DataType varchar（10）
）
声明@Foreign_keys表
（
Idval int identity（1,1），
ForeignKeyName varchar（1000），
ParentTable varchar（100）
）
声明
@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，
@Lo op_FK int，
@Drop_Key_Query nvarchar（1000）
BEGIN TRAN
插入@Identity_Details
（
SchemaName，
TableName，
ColumnName，
DataType
）
- 模式/表名/列名/数据类型等，标识插入列传递给临时表@Identity_Details
选择
c.TABLE_SCHEMA，
t.TABLE_NAME，
c.COLUMN_NAME，
c.DATA_TYPE
来自
information_schema.columns c
内部联接
information_schema.tables t
on t.TABLE_NAME = c.TABLE_NAME
其中
columnproperty（object_id（c.TABLE_NAME），c.COLUMN_NAME，'IsIdentity'）= 1
- 结束
- 带有_dup后缀和计数等的列名传递给临时表@Identity_Detail s $
选择
@ Leadingstring ='_ dup'，
@Count = count（*），
@循环= 1，
@NewColumn_Creation_Query =''，
@ UpdateColumn_Query =''，
@ DropColumn_Query =''，
@ Column_Rename_Query =''，
@ Primary_Key_Creation_Query =''
来自
@Identity_Details
--End
- 在临时表中逐个传递标识表，同时在While循环中逐一列出表/列/模式名称br />
while（@ Loop< = @ Count）
begin
select
@SchemaName = SchemaName，
@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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
--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 Passed
declare @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 TRAN
insert @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 c
inner join
information_schema.tables t
on t.TABLE_NAME = c.TABLE_NAME
where
columnproperty(object_id(c.TABLE_NAME),c.COLUMN_NAME, 'IsIdentity') = 1
--End
-- Column Names with _dup suffixed and count etc passed to Temporary Table @Identity_Details
select
@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 Loop
while(@Loop<=@Count)
begin
select
@SchemaName = SchemaName,
@TableName = TableName,
@ColumnName = ColumnName,
@DataType = DataType
from @Identity_Details
where 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 Column
select @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 Tables
insert @Foreign_keys (ForeignKeyName,ParentTable)
select name,object_name([parent_object_id])
from sys.foreign_keys
where object_name([referenced_object_id]) = @TableName
select
@Count_FK = max(IDVal),
@Loop_FK = MIN(IdVal)
from
@Foreign_keys
while(@Loop_FK<=@Count_FK)
begin
select @Drop_Key_Query = 'ALTER TABLE ' + ParentTable + ' DROP CONSTRAINT ' + ForeignKeyName
from @Foreign_keys where Idval = @Loop_FK
--select @Drop_Key_Query
exec sp_executesql @Drop_Key_Query
select @Drop_Key_Query = ''
select @Loop_FK = @Loop_FK + 1
end
Print 'All the Foreign key Constriants Dropped'
--End
---exec DropAllColumnConstraints @TableName,@ColumnName
--Drop All Column Constraints
while 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+'"')
end
Print 'Primary key Constriants Dropped'
--End
--Already Existing Columns dropped afterwards.
select @DropColumn_Query = 'ALTER TABLE '+ @SchemaName + '.'+@TableName + ' DROP COLUMN ' + @ColumnName
--PRINT @DropColumn_Query
exec 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_Query
exec 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_Query
exec sp_executesql @NewColumn_Creation_Query
Print 'New Column Name Data Type set as Not Nullable'
--End
--Primary Key Constraint Added for Identity Insert Columns
select @Primary_Key_Creation_Query = 'ALTER TABLE ' + @SchemaName + '.' + @TableName + ' add constraint ' + 'PK_' + @TableName + +'_' + @ColumnName+' primary key(' + '['+@ColumnName+']' + ')'
--Print @Primary_Key_Creation_Query
exec sp_executesql @Primary_Key_Creation_Query
Print 'Primary Key Constraint Added for Primary Key Columns'
--End
select
@Loop = @Loop + 1,
@NewColumn_Creation_Query = '',
@UpdateColumn_Query = '',
@Column_Rename_Query = '',
@Primary_Key_Creation_Query = '',
@DropColumn_Query = ''
delete from @Foreign_keys
end
COMMIT 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执行语句设置转到错误处理程序并回滚？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
