I am trying to save log details to a table in SQL. I want to catch both success and errors during SQL operations. Even when there is an SQL exception occured, I want to feed that as well.
I have set up an output parameter, and depending on whether it's success or error, its value changes. While calling this SP I'm getting an error like
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1. A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back
Below is the complete SP.
ALTER PROCEDURE [dbo].[Proc_PIP_Employee]
@Flag int,
@Empid int,
@Name varchar(500),
@Designation varchar(200),
@Department varchar(200),
@DateofJoin datetime,
@Phone varchar(10),
@Isactive int,
@LoginUser nvarchar(500),
@ReturnId int output
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @errorMessage VarChar(8000),
@errorSeverity Int,
@errorState Int,
@AlCode varchar(50),
@AlDesc varchar(1000),
@AlOp varchar(50),
@AlStatus varchar(50)
BEGIN TRY
BEGIN TRANSACTION
IF (@Flag = 1)
BEGIN
IF EXISTS (SELECT 1 FROM dbo.PIP_Employee
GROUP BY Name, Phone
HAVING COUNT(ID) > 0
AND Name = @Name AND Phone = @Phone)
BEGIN
SET @ReturnId = 0
SET @AlCode = 'ERR_1001'
SET @AlDesc = CONCAT('Add Record of ',@Name,' failed due to duplicate entry')
SET @AlOp = 'ADD'
SET @AlStatus = 'ERROR'
GOTO AuditLog
END
ELSE
BEGIN
INSERT INTO dbo.PIP_Employee (Name, Designation, Department, DateofJoin, Phone, IsPresent)
VALUES (@Name, @Designation, @Department, @DateofJoin, @Phone, @Isactive)
SET @ReturnId = 1
SET @AlCode = 'SUCC_1002'
SET @AlDesc = CONCAT('Record of ',@Name,' added Successfully')
SET @AlOp = 'ADD'
SET @AlStatus = 'SUCCESS'
GOTO AuditLog
END
return @ReturnId
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SET @ReturnId = 0
SET @AlCode = ERROR_SEVERITY()
SET @AlDesc = ERROR_MESSAGE()
SET @AlOp = 'SQL TRANSACTION FAILURE'
SET @AlStatus = ERROR_STATE();
IF (@@trancount > 0)
ROLLBACK TRANSACTION
GOTO AuditLog
RETURN @ReturnId
END CATCH
AuditLog:
INSERT INTO dbo.PIP_AuditLog (Aud_Code, Aud_Desc, Aud_Operation, Aud_Status, Aud_Createddate, Aud_ActionBy)
VALUES (@AlCode, @AlDesc, @AlOp, @AlStatus, GETDATE(), @LoginUser)
SET NOCOUNT OFF
END
