Cuando trabajamos con bases de datos, tarde o temprano llega la necesidad de tener que preparar scripts para actualizar el modelo de datos en el cliente, en un servidor no accesible, etc. Esto trae una carga importante de riesgos, sobre todo de pérdida de información o de dejar la base en un estado inconsistente.
Para asegurarnos de que todo quede en un estado conocido, en principio podemos abrir una transacción y poner todo el script en un bloque TRY-CATCH como este:
BEGIN TRY
BEGIN TRANSACTION
--
-- Poner acá el script con los cambios de la base
--
-- Aceptar los cambios de la transacción
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Deshacer los cambios de la transacción
ROLLBACK TRANSACTION
--
-- Manejar el error o relanzarlo
--
END CATCH
Ahora, supongamos que nuestros cambios son los siguientes:
CREATE TABLE Sucursales (
IdAlmacen INT,
Direccion VARCHAR(100)
)
GO
-- Otros cambios
GO
DELETE Articulos
WHERE IdCategoria = 9999
GO
-- Cambios sensibles y peligrosos
GO
CREATE PROCEDURE HacerNegocios AS
BEGIN
--
-- Aca va la formula mágica de cada uno
--
END
GO
-- ...mas cambios peligrosos!
GO
Para ponerlo dentro del bloque TRY-CATCH, la única consideración seria borrar los "GO" ya que sino la transacción y el TRY quedarían incompletos, y el script no ejecutaría correctamente.
Nota: La palabra "GO" no es parte del lenguaje TSQL sino un comando (bastante estándar) interpretado por las aplicaciones como un separador de lotes de instrucciones. Para mas información: http://msdn.microsoft.com/es-es/library/ms188037.aspx .
Con esto surge un problema, el CREATE/ALTER PROCEDURE (también aplicado a Functions y Triggers) debe ser la primera instrucción del lote debido a una restricción de sintáxis de SQL Server
Para salvar este inconveniente, una solución es ejecutar la creación del stored procedure con Dynamic SQL. Quedando asi:
EXEC(N'CREATE PROCEDURE HacerNegocios AS
BEGIN
--
-- aqui va la formula magica de cada uno
--
SELECT * FROM CC
END')
Con esto, no hay ningún problema de crear o modificar un stored procedure dentro de una transacción.
BEGIN TRY
BEGIN TRANSACTION
CREATE TABLE Sucursales (
IdAlmacen INT,
Direccion VARCHAR(100)
)
-- Otros cambios
DELETE Articulos
WHERE IdCategoria = 9999
-- Cambios sensibles y peligrosos
EXEC(N'CREATE PROCEDURE HacerNegocios AS
BEGIN
--
-- aqui va la formula mágica de cada uno
--
END')
-- ...mas cambios peligrosos!
-- Aceptar los cambios de la transacción
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Deshacer los cambios de la transacción
ROLLBACK TRANSACTION
--
-- Manejar el error o relanzarlo
--
END CATCH
Espero que este pequeño "truco" facilite la generación de scripts para hacer actualizaciones en las bases de datos.