issue with the transaction rollback in STORED PROC

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

issue with the transaction rollback in STORED PROC

Author

Topic

sqlservernovice
Starting Member

9 Posts

Posted - 2010-08-18 : 14:44:29

All,

i am trying to insert into various tables with the same id . and i wanted to achieve that if at allthere is any error at any point, i wanted everything rollback and delete all the inserted rows from the root. I am a novice to SQL programming, but i am not sure where to fix it.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[CREATEASSET_MODIFIED_ADASD_ADASDA]
(
@AliasName varchar(50),
@Category varchar(50),
@Corporatecode varchar(50),
@id int output,
@customer int,
@ReaderID int,
@MAX_SUMMER decimal(3,2),
@MIN_SUMMER decimal(3,2),
@MAX_WINTER decimal(3,2),
@MIN_WINTER decimal(3,2),
@SummerStartDate date,
@WinterStartDate date,
@sign numeric(1,0)

)

AS

BEGIN TRANSACTION

SELECT @id = ISNULL(max(IdentifiedObjectId),0) + 1 from CIM.IdentifiedObject

INSERT into CIM.IdentifiedObject VALUES (@id,@AliasName , '', '', @AliasName, '', @id, NULL)
DECLARE @ERROR INT
SET @ERROR = @@ERROR
IF @@ERROR <>0 GOTO ERROR_HANDLER

INSERT INTO CIM.AssetContainer VALUES (@id)

SET @ERROR = @@ERROR
IF @@ERROR <>0 GOTO ERROR_HANDLER

INSERT INTO CIM.ASSET VALUES (@id,'1','VELCO',@Category,@corporatecode,1,null,null,null,null,null,null,null,null,null,null,null,null,1,1,@id,1)
SET @ERROR = @@ERROR
IF @@ERROR <>0 GOTO ERROR_HANDLER

INSERT INTO CIM.EndDeviceAsset VALUES (NULL,1,0,1,1,1,1,1,1,1,null,@id,1,@CUSTOMER,1,10)

SET @ERROR = @@ERROR
IF @@ERROR <>0 GOTO ERROR_HANDLER

INSERT INTO CIM.MeterAsset VALUES ( NULL,NULL,NULL,@id, 1)

SET @ERROR = @@ERROR
IF @@ERROR <>0 GOTO ERROR_HANDLER

INSERT INTO CIM_AUG.MeterAsset_Aug VALUES (@id,@ReaderID,@MAX_SUMMER,@MIN_SUMMER,@MAX_WINTER,@MIN_WINTER,@SummerStartDate,@WinterStartDate,@sign)

SET @ERROR = @@ERROR
IF @@ERROR <>0 GOTO ERROR_HANDLER

COMMIT TRANSACTION

ERROR_HANDLER:

IF @ERROR <> 0

BEGIN

declare @x table (
id int
)

insert into @x

select assetcontainerid from CIM.assetcontainer

DELETE FROM CIM.ASSETCONTAINER
DELETE FROM CIM.ASSET
DELETE FROM CIM.IDENTIFIEDOBJECT

where IdentifiedObjectId in (select id from @x)
end

begin

IF @@TRANCOUNT >0

ROLLBACK TRANSACTION
END

GO
--error----

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 547, Level 16, State 0, Procedure CREATEASSET_MODIFIED_ADASD, Line 61
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Customer1". The conflict occurred in database "CIMUPDATE", table "CIM.Customer", column 'customerID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure CREATEASSET_MODIFIED_ADASD, Line 69
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MeterAsset_EndDeviceAsset". The conflict occurred in database "CIMUPDATE", table "CIM.EndDeviceAsset", column 'endDeviceAssetID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure CREATEASSET_MODIFIED_ADASD, Line 77
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MeterAsset_Aug_CIM_ASSET". The conflict occurred in database "CIMUPDATE", table "CIM.MeterAsset", column 'meterAssetID'.
The statement has been terminated.
Msg 3903, Level 16, State 1, Procedure CREATEASSET_MODIFIED_ADASD, Line 94
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
60027

----

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-08-18 : 14:56:26

You can put this in a Try/Catch block The FK errors that you are getting is because you are not inserting into the tables in the proper order. If there is a FK on Table B that references Table A, then you need to insert into Table A first. The @@Error only returns the error code from the previous line. So, after the first insert, you declare the variable @Error. The @@Error returns the result of declaring the variable not the insert statement. Try the code below, but again the order of the inserts matters.


ALTER PROCEDURE [dbo].[CREATEASSET_MODIFIED_ADASD_ADASDA]                                                                                        
 (                                                                                                                                                
 @AliasName varchar(50),                                                                                                                          
 @Category varchar(50),                                                                                                                           
 @Corporatecode varchar(50),                                                                                                                      
 @id int output,                                                                                                                                  
 @customer int,                                                                                                                                   
 @ReaderID int,                                                                                                                                   
 @MAX_SUMMER decimal(3,2),                                                                                                                        
 @MIN_SUMMER decimal(3,2),                                                                                                                        
 @MAX_WINTER decimal(3,2),                                                                                                                        
 @MIN_WINTER decimal(3,2),                                                                                                                        
 @SummerStartDate date,                                                                                                                           
 @WinterStartDate date,                                                                                                                           
 @sign numeric(1,0)                                                                                                                               
                                                                                                                                                 
 )                                                                                                                                                
                                                                                                                                                 
 AS                                                                                                                                               
begin
DECLARE @ErrorNum INT,
        @ErrorMessage varchar(200)
                                                                                                                                                 
    begin try                                                                                                                                                 
     BEGIN TRANSACTION                                                                                                                                
         -- this looks like it is getting the current identity in an identity column.  If
         -- it is then, you don't need to do this.  You can insert the row and then
         -- select @id = @@identity
         SELECT @id = ISNULL(max(IdentifiedObjectId),0) + 1 from CIM.IdentifiedObject                                                                     
                                                                                                                                                     
        INSERT into CIM.IdentifiedObject VALUES (@id,@AliasName , '', '', @AliasName, '', @id, NULL)                                                     
           
                                                                                                                                                     
        INSERT INTO CIM.AssetContainer VALUES (@id)                                                                                                      
                                                                                                                                                     
        INSERT INTO CIM.ASSET VALUES (@id,'1','VELCO',@Category,@corporatecode,1,null,null,null,null,null,null,null,null,null,null,null,null,1,1,@id,1)  
                                                                                                                                                     
        INSERT INTO CIM.EndDeviceAsset VALUES (NULL,1,0,1,1,1,1,1,1,1,null,@id,1,@CUSTOMER,1,10)                                                         
        
        INSERT INTO CIM.MeterAsset VALUES ( NULL,NULL,NULL,@id, 1)                                                                                       
        
        INSERT INTO CIM_AUG.MeterAsset_Aug VALUES (@id,@ReaderID,@MAX_SUMMER,@MIN_SUMMER,@MAX_WINTER,@MIN_WINTER,@SummerStartDate,@WinterStartDate,@sign)
     COMMIT TRANSACTION                                                                                                                               
    end try
    begin catch
      Rollback Tran
      select @errorNum = Error_Number(),
             @ErrorMessage = Error_Message()
      RaisError(@ErrorMessage, 16, 1)
    end catch                                                                                                                                                            
 END

sqlservernovice
Starting Member

9 Posts

Posted - 2010-08-18 : 15:23:41

Thank you for such an elegant code. It works perfectly fine for me.
I have another question related to the same procedure. I have to drop and create a constraint every time i do this insert. Is there a way that we can incorporate in the code?

i tried doing it, and it was giving me errors. wondering if i can do a ddl along with a dml operation in the same procedure.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-19 : 13:24:47

quote:
Originally posted by sqlservernovice

Thank you for such an elegant code. It works perfectly fine for me.
I have another question related to the same procedure. I have to drop and create a constraint every time i do this insert. Is there a way that we can incorporate in the code?

i tried doing it, and it was giving me errors. wondering if i can do a ddl along with a dml operation in the same procedure.

you can do ddl in same procedure. Do ddl stuff together.
B/w whats the constraint you're trying to delete and recreate each time?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources