Abrir transação em uma SP e fechar a mesma transação em outra SP

Question

Duvida simples pessoal. Posso fechar uma transação iniciada em outra procedure?

Exemplo:

CREATE PROCEDURE UsuariosIncluirReg
@flag bit output,
AS
DECLARE
@sql nvarchar(max)

BEGIN

BEGIN TRANSACTION UsuariosIncluirReg
--Chama a procedure que executa a query
EXEC ExecutaQuery @flag output, @sql, 'UsuariosIncluirReg'
SELECT @flag AS flag
END
GO

CREATE PROCEDURE ExecutaQuery
@flag int output,
@sqlquery nvarchar(max),
@transacao nvarchar(128)
AS
BEGIN
BEGIN TRY
EXEC ( @sqlquery )
SELECT @@IDENTITY AS [@@IDENTITY];
IF @@TRANCOUNT > 0
BEGIN
COMMIT TRANSACTION @transacao;
END
SET @flag=1;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION @transacao;
END
EXEC @flag = RetornaErroBd @flag output;
SELECT @flag AS flag
RETURN @flag;
END CATCH
END
GO

Isso etá funcionando... Mas estou recebendo uma mensagem: "O número de transações após EXECUTE indica um número incompatível das instruções BEGIN e COMMIT. Número anterior = 1, número atual = 0."

Answer 1

Pessoal... tentei depurar o script e...

Percebi que quando executa a linha BEGIN TRY ele pasa o @@TRANCOUNT para 2 e o pior é que quando chega na linha IF @@TRANCOUNT > 0 aí @@TRANCOUNT vai para 3 o SQL Server trava tudo e tenho que fechar e abrir o SQL Manager outra vez.

Answer 2

Bom dia,

Verifique essa thread.

Atenciosamente,

---

Robson William Silva

Esse conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita

MSDN Community Support

Por favor, lembre-se de Marcar como Resposta as postagens que resolveram o seu problema. Essa é uma maneira comum de reconhecer aqueles que o ajudaram e fazer com que seja mais fácil para os outros visitantes encontrarem a resolução mais tarde.

Answer 3

Alam,

Cada bloco Begin que você declara ou o SQL Server inicia representa uma transação!!!

Talvez no seu caso seja mais indicado trabalhar com o conceito de transações explícitas e implícitas onde você determina quando deve começar e terminar um transação.

Veja se este exemplo poderá te ajudar:

SET NOCOUNT ON;
GO

SET IMPLICIT_TRANSACTIONS OFF;
GO

PRINT N'Tran count at start = '
    + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO

IF OBJECT_ID(N'dbo.t1',N'U') IS NOT NULL
    DROP TABLE dbo.t1;
GO

CREATE table dbo.t1 (a int);
GO

INSERT INTO dbo.t1 VALUES (1);
GO

PRINT N'Use explicit transaction.';
BEGIN TRANSACTION;
GO

INSERT INTO dbo.t1 VALUES (2);
GO

PRINT N'Tran count in explicit transaction = '
    + CAST(@@TRANCOUNT AS NVARCHAR(10));
COMMIT TRANSACTION;
GO

PRINT N'Tran count after explicit transaction = '
    + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO

PRINT N'Setting IMPLICIT_TRANSACTIONS ON.';
GO
SET IMPLICIT_TRANSACTIONS ON;
GO

PRINT N'Use implicit transactions.';
GO

-- No BEGIN TRAN needed here.
INSERT INTO dbo.t1 VALUES (4);
GO

PRINT N'Tran count in implicit transaction = '
    + CAST(@@TRANCOUNT AS NVARCHAR(10));
COMMIT TRANSACTION;

PRINT N'Tran count after implicit transaction = '
    + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO

PRINT N'Nest an explicit transaction with IMPLICIT_TRANSACTIONS ON.';
GO

PRINT N'Tran count before nested explicit transaction = '
    + CAST(@@TRANCOUNT AS NVARCHAR(10));

BEGIN TRANSACTION;

PRINT N'Tran count after nested BEGIN TRAN in implicit transaction = '
    + CAST(@@TRANCOUNT AS NVARCHAR(10));

INSERT INTO dbo.t1 VALUES (5);
COMMIT TRANSACTION;

PRINT N'Tran count after nested explicit transaction = '
    + CAST(@@TRANCOUNT AS NVARCHAR(10));
GO

-- Commit outstanding transaction.
COMMIT TRANSACTION;
GO

/* ********************************** */
/* Transações Explicitas                         */
/* ********************************** */
CREATE TABLE TESTE_UM
(
  Num int NOT NULL

  CONSTRAINT PK_TesteUM 
  PRIMARY KEY(Num) 
)

CREATE TABLE TESTE_Dois
(
  letra char(1) NOT NULL 

  CONSTRAINT PK_TesteDois
  PRIMARY KEY(letra) 
)

select * from Teste_Um
select * from Teste_Dois

/* *************************** */
BEGIN TRANSACTION 

 INSERT Teste_Um values(2)
 IF @@ERROR <> 0  
  BEGIN 
     ROLLBACK TRANSACTION  
     RETURN
  END

  SELECT * FROM Teste_Um

  INSERT Teste_Dois values('A') 
  IF @@ERROR <> 0
  BEGIN 
     ROLLBACK TRANSACTION   
     print 'VOltei aqui'
     RETURN
  END
COMMIT TRANSACTION
/* *************************** */
SELECT * FROM TESTE_UM
SELECT * FROM TESTE_DOIS
/* *************************** */

---

Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]