How to set go to error handler and roll back for each SQL execution statements in SQL?

Question

1.00/5 (1 vote)

See more:

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 &#39;400220101&#39;,&#39;&#39;,&#39;&#39;  
--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, &#39;IsIdentity&#39;) = 1 
	--End

   -- Column Names with _dup suffixed and count etc passed to Temporary Table @Identity_Details
	select 
	@Leadingstring= &#39;_dup&#39;,
	@Count        =count(*),
	@Loop		  = 1,
	@NewColumn_Creation_Query = &#39;&#39;,
	@UpdateColumn_Query=&#39;&#39;,
	@DropColumn_Query=&#39;&#39;,
	@Column_Rename_Query=&#39;&#39;,
	@Primary_Key_Creation_Query=&#39;&#39;
	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&lt;=@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 = &#39;ALTER TABLE &#39; + @SchemaName + &#39;.&#39;+ @TableName + &#39; ADD &#39; +@ColumnName  + @Leadingstring + &#39; &#39; +  @DataType 

	exec sp_executesql @NewColumn_Creation_Query

	 Print &#39;New Column Creation Completed Successfully&#39;
	 --End
     --To each identity update data in the Temp Column newly created (_dup) to Real Column
	select @UpdateColumn_Query = &#39;UPDATE &#39; + @SchemaName + &#39;.&#39;+@TableName + &#39; SET &#39; + @ColumnName +  @Leadingstring+ &#39; = &#39; + @ColumnName 

	exec sp_executesql 	@UpdateColumn_Query
  
	Print &#39;New Column Values Updated with Existing Values&#39;
	--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&lt;=@Count_FK)
	begin
		select @Drop_Key_Query =  &#39;ALTER TABLE &#39; + ParentTable + &#39; DROP CONSTRAINT &#39; + ForeignKeyName
		from @Foreign_keys where Idval = @Loop_FK
        --select  @Drop_Key_Query
		exec sp_executesql 	@Drop_Key_Query
		select @Drop_Key_Query = &#39;&#39;
		select @Loop_FK =  @Loop_FK + 1

	end
	
   
	Print &#39;All the Foreign key Constriants Dropped&#39;
   --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 (&#39;alter table &quot;&#39;+@tableName+&#39;&quot; drop constraint &quot;&#39;+@constraintName+&#39;&quot;&#39;)
      exec (&#39;alter table &quot;&#39;+@TableName+&#39;&quot; drop constraint &quot;&#39;+@constraintName+&#39;&quot;&#39;)
    end
	Print &#39;Primary key Constriants Dropped&#39;
	--End
	
     --Already Existing Columns dropped afterwards. 
	select @DropColumn_Query = &#39;ALTER TABLE &#39;+ @SchemaName + &#39;.&#39;+@TableName + &#39; DROP COLUMN &#39; + @ColumnName
	--PRINT @DropColumn_Query
	exec sp_executesql 	@DropColumn_Query 
	Print &#39;Already Exisiting Columns Dropped&#39;
	--End

    --Rename Columns with_dup suffixed to Column already existed before   
	select @Column_Rename_Query =  &#39;exec sp_rename &#39; + &#39;&#39;&#39;&#39; + @SchemaName + &#39;.&#39; + @TableName + &#39;.&#39; +@ColumnName + @Leadingstring +&#39;&#39;&#39;&#39; + &#39;,&#39;  + &#39;&#39;&#39;&#39; + @ColumnName + &#39;&#39;&#39;&#39; + &#39;,&#39; + &#39;&#39;&#39;&#39; +&#39;COLUMN&#39; +&#39;&#39;&#39;&#39;
	-- Print @Column_Rename_Query
	exec sp_executesql 	@Column_Rename_Query 
	Print &#39;Column Name Renamed from Temporary to Original Existing Column Names&#39;
	--End

    --Set the New Column renamed as not null    
	select @NewColumn_Creation_Query = &#39;ALTER TABLE &#39; + @SchemaName + &#39;.&#39;+ @TableName + &#39; ALTER COLUMN &#39; +@ColumnName  + &#39; &#39; + @DataType  + &#39; Not Null&#39;
	--PRINT  @NewColumn_Creation_Query
	exec sp_executesql 	@NewColumn_Creation_Query
	Print &#39;New Column Name Data Type set as Not Nullable&#39;
    --End
    
    --Primary Key Constraint Added  for Identity Insert Columns
	select @Primary_Key_Creation_Query = &#39;ALTER TABLE &#39; +  @SchemaName + &#39;.&#39; + @TableName  + &#39; add constraint &#39; + &#39;PK_&#39; + @TableName + +&#39;_&#39; + @ColumnName+&#39; primary key(&#39; + &#39;[&#39;+@ColumnName+&#39;]&#39; + &#39;)&#39;
	--Print @Primary_Key_Creation_Query
	exec sp_executesql 	@Primary_Key_Creation_Query 
	Print &#39;Primary Key Constraint Added for Primary Key Columns&#39;
	--End	
	
	select 
		@Loop = @Loop + 1,
		@NewColumn_Creation_Query = &#39;&#39;,
		@UpdateColumn_Query = &#39;&#39;,
		@Column_Rename_Query = &#39;&#39;,
		@Primary_Key_Creation_Query = &#39;&#39;,
		@DropColumn_Query = &#39;&#39;
		
		delete from @Foreign_keys


end
COMMIT TRAN</pre>

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

Posted 11-Apr-16 3:56am

ranio

Updated 11-Apr-16 5:25am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ivan_FM · Answer 1 · 2016-04-11T05:25:00

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