Saludos espero puedan ayudarme.
Resulta que tengo un procedimiento almacenado que me esta generando el siguiente error del SQL:
La solicitud ROLLBACK TRANSACTION no tiene la correspondiente BEGIN TRANSACTION.
El error en el store cuando uso el metodo ERROR_MESSAGE() es el Siguiente:
El recuento de transacciones después de EXECUTE indica un número no coincidente de instrucciones BEGIN y COMMIT. Recuento anterior = 1, recuento actual = 0. en la línea 0.
No entiendo porque si en realidad si tengo El respectivo Begin Transaction, Commit Transaction y Rollback Transaction. El Código es el siguiente:
Código SQL:
Ver originalPROCEDURE [dbo].[addPosAndStore]
@idStrClient AS VARCHAR(50),
@statusList AS text,
@nameList AS text,
@posidList AS text,
@nodesList AS text,
@msg AS VARCHAR(200) output
AS
BEGIN
SET NOCOUNT ON
DECLARE @idClient BIGINT,
@idConstraint BIGINT,
@posidConstraint BIGINT,
@name VARCHAR(100),
@posId VARCHAR(100),
@businessIdNode VARCHAR(100),
@STATUS tinyint,
@totalRows INT,
@counter INT,
@idHd BIGINT,
@idHierarchyData BIGINT,
@retValue INT
BEGIN TRY
BEGIN TRANSACTION Tadd
SELECT * INTO #tstatus FROM Split(@statusList,',')
SELECT * INTO #tname FROM Split(@nameList,',')
SELECT * INTO #posid FROM Split(@posidList,',')
SELECT * INTO #nodes FROM Split(@nodesList,',')
SET @totalRows = (SELECT COUNT(*) FROM #tstatus)
SET @counter = 0
WHILE @counter <= @totalRows
BEGIN
SET @STATUS = CONVERT(TINYINT,(SELECT VALUE FROM #tstatus WHERE id = @counter))
SET @name = (SELECT VALUE FROM #tname WHERE id = @counter)
SET @posId = (SELECT VALUE FROM #posid WHERE id = @counter)
SET @businessIdNode = (SELECT VALUE FROM #nodes WHERE id = @counter)
SET @idClient = (SELECT id FROM Client WHERE idStrClient = @idStrClient)
EXEC @retValue = [dbo].[addStore]
@name = @name,
@businessIdNode = @businessIdNode,
@idClient = @idClient,
@idHd = @idHd OUTPUT,
@msg = @msg output
SET @idHierarchyData = (SELECT @idHd)
IF @idHierarchyData = -1 OR @idHierarchyData IS NULL
RETURN
INSERT INTO TableConstraint (openingSchedule, closingSchedule,salesLimitByDay,salesAmountByDay,salesLimitByMonth,salesAmountByMonth) VALUES (NULL, NULL, 0.0, 0, 0.0, 0)
SET @posidConstraint = (SELECT MAX(id) FROM TxnConstraint)
INSERT INTO [POS]
([idClient],[idHierarchyData],[POSIdentifier],[TYPE],[STATUS],[idConstraint])
VALUES
(@idClient,@idHierarchyData,@posId,0,@STATUS,@posidConstraint)
SET @counter = @counter + 1
END
DROP TABLE #tname
DROP TABLE #tstatus
DROP TABLE #posid
COMMIT TRANSACTION Tadd
SET @msg = '00,Se han insertado los registros correctamente'
END TRY
BEGIN CATCH
SET @msg = '99,Ocurrio un Error: '+ERROR_MESSAGE()+' en la línea ' + CONVERT(NVARCHAR(255), ERROR_LINE() ) + '.'
ROLLBACK TRANSACTION Tadd
END CATCH
END
GO