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.
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.
@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)
begin
set @ErrorMessage = 'Corporate Code ' +
@Corporatecode + ' already exists.'
raiserror(@ErrorMessage, 16, 1);
end;
