No se ejecuta mi procedimiento

[Developer9]

HOla... disculpen a alguien le ha pasado que primero crea un procedimiento y luego crea un segundo procedimiento en el que manda a ejecutar el primer procedimiento; ejecuto el segundo procedimiento y no da errores pero si le hago select a las tablas que modifica el primer procedimento no se ha producido ningún cambio

[Andres95]

podria ser error de transacciones que hace rollback...
o bien simplemente no esta entrando en la opcion de modifcar las tablas....

[Developer9]

ya lo corregí, era un error tonto.

aunque me surgieron otras dudas, teniendo un procedimiento1 que llama a un procedimiento2, y el codigo es mas o menos así:

Código:

Procedimiento1

BEGIN TRANS

INSERT INTO...
INSERT INTO...

exec Procedimiento2

UPDATE ...
INSERT...

IF (@@error <> 0)
BEGIN
	ROLLBACK TRAN
	RETURN
END

COMMIT

Y en el Procedimiento2 tambien tengo inserts y updates debo de iniciar una transaccion tambien? o basta con la transaccion iniciada en el procedimiento1. Y si inicio dos transacciones (una en cada procedimiento) que pasaría en el caso de algún error en procedimiento2, se hace rollback a ambas transacciones?

[Mithrandir]

Esto es la ayuda de SQL Server, happy reading!:

Iniciado por BOLs

Nesting Transactions
Explicit transactions can be nested. This is primarily intended to support transactions in stored procedures that can be called either from a process already in a transaction or from processes that have no active transaction.

The following example shows the intended use of nested transactions. The procedure TransProc enforces its transaction regardless of the transaction mode of any process that executes it. If TransProc is called when a transaction is active, the nested transaction in TransProc is largely ignored, and its INSERT statements are committed or rolled back based on the final action taken for the outer transaction. If TransProc is executed by a process that does not have an outstanding transaction, the COMMIT TRANSACTION at the end of the procedure effectively commits the INSERT statements.

SET QUOTED_IDENTIFIER OFF
GO
SET NOCOUNT OFF
GO
USE pubs
GO
CREATE TABLE TestTrans(Cola INT PRIMARY KEY,
Colb CHAR(3) NOT NULL)
GO
CREATE PROCEDURE TransProc @PriKey INT, @CharCol CHAR(3) AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@PriKey, @CharCol)
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol)
COMMIT TRANSACTION InProc
GO
/* Start a transaction and execute TransProc */
BEGIN TRANSACTION OutOfProc
GO
EXEC TransProc 1, 'aaa'
GO
/* Roll back the outer transaction, this will
roll back TransProc's nested transaction */
ROLLBACK TRANSACTION OutOfProc
GO
EXECUTE TransProc 3,'bbb'
GO
/* The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent rollback. */
SELECT * FROM TestTrans
GO

Committing inner transactions is ignored by Microsoft® SQL Server™. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.

Each call to COMMIT TRANSACTION or COMMIT WORK applies to the last executed BEGIN TRANSACTION. If the BEGIN TRANSACTION statements are nested, then a COMMIT statement applies only to the last nested transaction, which is the innermost transaction. Even if a COMMIT TRANSACTION transaction_name statement within a nested transaction refers to the transaction name of the outer transaction, the commit applies only to the innermost transaction.

It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all the nested transactions, including the outermost transaction.

The @@TRANCOUNT function records the current transaction nesting level. Each BEGIN TRANSACTION statement increments @@TRANCOUNT by one. Each COMMIT TRANSACTION or COMMIT WORK statement decrements @@TRANCOUNT by one. A ROLLBACK WORK or a ROLLBACK TRANSACTION statement that does not have a transaction name rolls back all nested transactions and decrements @@TRANCOUNT to 0. A ROLLBACK TRANSACTION that uses the transaction name of the outermost transaction in a set of nested transactions rolls back all the nested transactions and decrements @@TRANCOUNT to 0. When you are unsure if you are already in a transaction, SELECT @@TRANCOUNT to determine if it is 1 or more. If @@TRANCOUNT is 0 you are not in a transaction.