salida - ¿Cómo trata SQL Server las declaraciones dentro de los procedimientos almacenados con respecto a las transacciones?

ejercicios resueltos de procedimientos almacenados en sql server

Question

Digamos que tengo un procedimiento almacenado que consiste en varias instrucciones separadas SELECT, INSERT, UPDATE y DELETE. No hay una lógica BEGIN TRANS / COMMIT TRANS / ROLLBACK TRANS explícita.

¿Cómo manejará SQL Server este procedimiento almacenado de forma transaccional? ¿Habrá una conexión implícita para cada declaración? ¿O habrá una transacción para el procedimiento almacenado?

Además, ¿cómo podría haberlo descubierto solo con T-SQL y / o SQL Server Management Studio?

¡Gracias!

Answer 1

Puede averiguarlo por su cuenta creando un pequeño procedimiento almacenado que hace algo simple, por ejemplo, insertar un registro en una tabla de prueba. Entonces comienza Tran; ejecutar sp_test; Retroceder; Es el nuevo disco allí? Si es así, el SP ignora la transacción externa. Si no, entonces el SP es solo otra declaración ejecutada dentro de la transacción (que estoy bastante seguro de que es el caso).

Answer 2

Solo habrá una conexión, es lo que se usa para ejecutar el procedimiento, sin importar cuántos comandos SQL haya dentro del procedimiento almacenado.

ya que no tiene una BEGIN TRANSACTION explícita en el procedimiento almacenado, cada instrucción se ejecutará por sí misma sin posibilidad de deshacer ningún cambio si hay algún error.

Sin embargo, si antes de llamar al procedimiento almacenado emite una BEGIN TRANSACTION, todas las declaraciones se agrupan dentro de una transacción y pueden ser COMMITted o ROLLBACKed después de la ejecución del procedimiento almacenado.

Desde dentro del procedimiento almacenado, puede determinar si se está ejecutando dentro de una transacción al verificar el valor de la variable del sistema @@ TRANCOUNT (Transact-SQL) . Un cero significa que no hay transacción, cualquier otra cosa muestra el nivel de transacciones anidado en el que se encuentra. Dependiendo de su versión de servidor sql, también podría usar XACT_STATE (Transact-SQL) .

Si haces lo siguiente:

BEGIN TRANSACTION

EXEC my_stored_procedure_with_5_statements_inside @Parma1

COMMIT

todo dentro del procedimiento está cubierto por la transacción, las 6 declaraciones (el EXEC es una declaración cubierta por la transacción, 1 + 5 = 6). Si haces esto:

BEGIN TRANSACTION

EXEC my_stored_procedure_with_5_statements_inside @Parma1
EXEC my_stored_procedure_with_5_statements_inside @Parma1

COMMIT

todo lo que está dentro de las dos llamadas a procedimientos está cubierto por la transacción, las 12 declaraciones (los 2 EXEC son ambas declaraciones cubiertas por la transacción, 1 + 5 + 1 + 5 = 12).