April 29, 2013 at 2:45 am
hi,
i have below code
declare @V_AreaId int = 1
declare @V_Area nvarchar(30)='xxx'
declare @V_Pincode nvarchar(10) ='35646'
BEGIN TRY
BEGIN TRANSACTION
IF NOT EXISTS (SELECT 1 FROM Mas_Area WHERE AreaId = @V_AreaId)
BEGIN
IF EXISTS(SELECT * FROM Mas_Area WHERE Area = @V_Area)
BEGIN
SELECT 'Area already exists' AS Msg
RETURN;
Print @@TRANCOUNT
END
ELSE IF EXISTS(SELECT * FROM Mas_Area WHERE Pincode = @V_Pincode)
BEGIN
SELECT 'Pincode already exists' AS Msg
RETURN;
Print @@TRANCOUNT
END
ELSE
BEGIN
INSERT INTO Mas_Area
(Area,DistrictId,Pincode, CreatedBy, CreatedOn , Status, Reason)
VALUES
(@V_Area,12,@V_Pincode, 'dhii', GETDATE(), 'true', null)
SELECT @V_AreaId = IDENT_CURRENT('Mas_Area')
Print @@TRANCOUNT
END
END
ELSE
BEGIN
IF EXISTS(SELECT * FROM Mas_Area WHERE AreaId <> @V_AreaId AND Area = @V_Area)
BEGIN
SELECT 'Area already exists' AS Msg
RETURN;
END
ELSE IF EXISTS(SELECT * FROM Mas_Area WHERE AreaId <> @V_AreaId AND Pincode = @V_Pincode)
BEGIN
SELECT 'Pincode already exists' AS Msg
RETURN;
END
ELSE
BEGIN
UPDATE
Mas_Area
SET
Area = @V_Area,
Pincode=@V_Pincode
WHERE
AreaId = @V_AreaId
END
END
SELECT @V_AreaId, 'SUCCESS' AS Msg
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT ERROR_NUMBER() as ErrorNumber,ERROR_MESSAGE() as ErrorMessage;
INSERT INTO ERRORLOG
(PatientNumber, SP_Name, ErrorNo, ErrorMsg, ErrorDt, ModuleName, FormName)
VALUES
('NA','USP_Mas_Area_InsertUpdate', ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE(), 'Masters', 'Area')
END CATCH
when i run this for second time I am getting Error
''''Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.''
no of begin and end is Equal but still getting error
Please need help.
thanks,
April 29, 2013 at 3:05 am
Presumably you're hitting an error in your TRY block and breaking out of it before the COMMIT TRANSACTION statement is reached. Can you not lose the explicit transaction - I can't see why it's necessary?
John
April 29, 2013 at 3:46 am
You don't need all those RETURN statements in your code:
declare @V_AreaId int = 1
declare @V_Area nvarchar(30)='xxx'
declare @V_Pincode nvarchar(10) ='35646'
BEGIN TRY
BEGIN TRANSACTION
IF NOT EXISTS (SELECT 1 FROM Mas_Area WHERE AreaId = @V_AreaId)
BEGIN
IF EXISTS(SELECT * FROM Mas_Area WHERE Area = @V_Area)
BEGIN
SELECT 'Area already exists' AS Msg
Print @@TRANCOUNT
END
ELSE IF EXISTS(SELECT * FROM Mas_Area WHERE Pincode = @V_Pincode)
BEGIN
SELECT 'Pincode already exists' AS Msg
Print @@TRANCOUNT
END
ELSE
BEGIN
INSERT INTO Mas_Area(Area,DistrictId,Pincode, CreatedBy, CreatedOn , Status, Reason)
VALUES (@V_Area,12,@V_Pincode, 'dhii', GETDATE(), 'true', null)
SELECT @V_AreaId = IDENT_CURRENT('Mas_Area')
Print @@TRANCOUNT
SELECT @V_AreaId, 'SUCCESS' AS Msg
END
END
ELSE
BEGIN
IF EXISTS(SELECT * FROM Mas_Area WHERE AreaId <> @V_AreaId AND Area = @V_Area)
BEGIN
SELECT 'Area already exists' AS Msg
END
ELSE IF EXISTS(SELECT * FROM Mas_Area WHERE AreaId <> @V_AreaId AND Pincode = @V_Pincode)
BEGIN
SELECT 'Pincode already exists' AS Msg
END
ELSE
BEGIN
UPDATE Mas_Area SET
Area = @V_Area,
Pincode = @V_Pincode
WHERE
AreaId = @V_AreaId
SELECT @V_AreaId, 'SUCCESS' AS Msg
END
END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT ERROR_NUMBER() as ErrorNumber,ERROR_MESSAGE() as ErrorMessage;
INSERT INTO ERRORLOG (PatientNumber, SP_Name, ErrorNo, ErrorMsg, ErrorDt, ModuleName, FormName)
VALUES ('NA','USP_Mas_Area_InsertUpdate', ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE(), 'Masters', 'Area')
END CATCH
April 29, 2013 at 8:22 pm
You probably do not need all that excessive "in code" checking.
The whole point of those checks is to make sure there would not be any error.
Which is good (as for me). I personally do not thinks "programming by exception" is good in DB environment.
I prefer to prevent errors rather than catch them.
But if you chose to use TRY..CATCH construct then take all its advantages.
declare @V_AreaId int = 1
declare @V_Area nvarchar(30)='xxx'
declare @V_Pincode nvarchar(10) ='35646'
BEGIN TRY
BEGIN TRANSACTION
UPDATE Mas_Area
SET
Area = @V_Area,
Pincode=@V_Pincode
WHERE
AreaId = @V_AreaId
INSERT INTO Mas_Area
(Area,DistrictId,Pincode, CreatedBy, CreatedOn , Status, Reason)
SELECT @V_Area,12,@V_Pincode, 'dhii', GETDATE(), 'true', null
where NOT EXISTS (SELECT * FROM Mas_Area
WHERE AreaId = @V_AreaId)
SELECT @V_AreaId = IDENT_CURRENT('Mas_Area')
Print @@TRANCOUNT
SELECT @V_AreaId, 'SUCCESS' AS Msg
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT ERROR_NUMBER() as ErrorNumber,ERROR_MESSAGE() as ErrorMessage;
INSERT INTO ERRORLOG
(PatientNumber, SP_Name, ErrorNo, ErrorMsg, ErrorDt, ModuleName, FormName)
VALUES
('NA','USP_Mas_Area_InsertUpdate', ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE(), 'Masters', 'Area')
END CATCH
If you have UNIQUE constraints enforced on Area and Pincode than an attempt to insert a duplicate value will throw an error and start "CATCH" block. ERROR_MESSAGE() recorded in the log will tell you what exactly has happened.
Only sugestion - I'd save the combination of values caused the error together with the error message in the log.
And you can ditch the explicit transaction completely if you do a code like that.
Even if 2 or more sessions submit the same values (or different values with the same ID) to the table at the same moment the code won't go wrong, if you think it through.
And removing explicit transaction will reduce the overhead connected to it significantly.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply