Katie 1 avatar image
Katie 1 asked

Nested Transactions in the stored proc

Hi everybody, i was trying to insert the data into various tables with this stored procedure.. if any error occures the transaction should be rolled back. i have also a requirement to check.. that if there @corporate code already exists one of the tbales.. the transaction should not even proceed further and roll back at the transaction. I am getting an error, that i pasted below. The stored proc.. execute successfully. but then i try to execute it with the parameters. it kicks this error. Any help is appreciated. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[TEST] ( @AliasName varchar(50), @Category varchar(50), @Corporatecode varchar(50), @id int output, @customer int, @ReaderID int, @MAX_SUMMER NUMERIC(18,10), @MIN_SUMMER NUMERIC(18,10), @MAX_WINTER decimal(18,10), @MIN_WINTER decimal(18,10), @SummerStartDate date, @WinterStartDate date, @sign numeric(1,0) ) as begin declare @errorNum int, @ErrorMessage varchar(200); alter table CIM.AssetContainer nocheck constraint FK_AssetContainer_Asset; begin try begin tran; SELECT @id = ISNULL(max(IdentifiedObjectId),0) + 1 from CIM.IdentifiedObject INSERT into CIM.IdentifiedObject([identifiedObjectID],[aliasName],[description],[localName],[mRID],[name],[pathName],[ModelingAuthoritySet]) VALUES (@id,@AliasName , '', '', @AliasName, '', @id, NULL) INSERT INTO CIM.AssetContainer(assetcontainerID) VALUES (@id) Begin tran begin try INSERT INTO CIM.ASSET([assetID],[statusID],[application],[category],[corporateCode],[critical],[initialCondition],[initialLossOfLife],[installationDate],[lotNumber],[manufacturedDate],[purchasePrice],[serialNumber],[testDate],[testStatus],[testType],[utcNumber],[WorkTask],[DimensionsInfo],[ErpItemMaster],[AssetContainer],[FinancialInfo]) VALUES (@id,'1','VELCO',@Category,@corporatecode,1,null,null,null,null,null,null,null,null,null,null,null,null,1,1,@id,1) if exists (select * from cim.asset where corporatecode = @Corporatecode) PRINT 'ERROR' END TRY BEGIN CATCH if @@trancount > 0 rollback tran; END CATCH INSERT INTO CIM.EndDeviceAsset([amrSystem],[demandResponse],[disconnect],[dstEnabled],[loadControl],[metrology],[outageReport],[readRequest],[relayCapable],[reverseFlowHandling],[timeZoneOffset],[endDeviceAssetID],[EndDeviceModel],[Customer],[ServiceLocation],[ServiceDeliveryPoint]) VALUES (NULL,1,0,1,1,1,1,1,1,1,null,@id,1,@CUSTOMER,1,10) INSERT INTO CIM.MeterAsset([formNumber],[kH],[kR],[meterAssetID],[MeterAssetModel]) VALUES ( NULL,NULL,NULL,@id, 1) INSERT INTO CIM_AUG.MeterAsset_Aug ([assetmeterID],[ReaderID],[MAX_SUMMER],[MIN_SUMMER],[MAX_WINTER],[MIN_WINTER],[SummerStartDate],[WinterStartDate],[Sign]) VALUES (@id,@ReaderID,@MAX_SUMMER,@MIN_SUMMER,@MAX_WINTER,@MIN_WINTER,@SummerStartDate,@WinterStartDate,@sign) commit tran; end try begin catch if @@trancount > 0 rollback tran; select @errorNum = error_number(), @ErrorMessage = error_message(); end catch; alter table CIM.AssetContainer check constraint FK_AssetContainer_Asset; if @ErrorMessage is not null raiserror(@ErrorMessage, 16, 1); end; ERROR i get is >(1 row(s) affected) >1 row(s) affected) >ERROR >(1 row(s) affected) >(1 row(s) affected) >(1 row(s) affected) >Msg 266, Level 16, State 2, Procedure TEST, Line 58 Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Grant Fritchey avatar image
Grant Fritchey answered
Unless I'm reading this wrong (entirely possible) you have two BEGIN TRAN statements and only one COMMIT TRAN. You should have two COMMIT statements. Also, I might be wrong here, but the first TRY won't use that CATCH because there's another TRY in the way. Based on the fact that you're only using a single CATCH statement, I'd go ahead and get rid of that second TRY block.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@Katie What Grant suggests is correct, and because I have already seen your proc, i can suggest this: - remove inner try / catch block - remove inner tran completely, so your proc has only one try / catch and one begin tran / commit tran - remove the corporate code check and print 'error' under it - Add this check as the first statement after begin try / begin tran, but add the code like the one below in your **if** block:
if  exists (select * from cim.asset 
    where corporatecode = @Corporatecode)
    set @ErrorMessage = 'Corporate Code ' + 
        @Corporatecode + ' already exists.'
    raiserror(@ErrorMessage, 16, 1);

This way, if the corporate code already exists, the tran will rollback and the error - reported like you want. In a mean time, the proc will not even attempt any other operations, not even insert into CIM.IdentifiedObject. Basically, what you can do is take your original proc (not this version but the one earlier) and add the code displayed above under **begin tran;** line.
2 Likes 2 ·
Katie 1 avatar image Katie 1 commented ·
Thank you Oleg.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.