Agregar columna a la tabla y luego actualizarla dentro de la transacción


Estoy creando un script que se ejecutará en un servidor MS SQL. Este script ejecutará varias sentencias y debe ser transaccional, si una de las sentencias falla, la ejecución general se detiene y los cambios se revertirán.

Tengo problemas para crear este modelo transaccional al emitir sentencias ALTER TABLE para agregar columnas a una tabla y luego actualizar la columna recién agregada. Para acceder a la columna recién agregada de inmediato, utilizo un comando GO para ejecutar el ALTER TABLE statement, y luego llame a mi declaración de ACTUALIZACIÓN. El problema que estoy enfrentando es que no puedo emitir un comando GO dentro de una declaración IF. La declaración IF es importante dentro de mi modelo transaccional. Este es un código de ejemplo del script que estoy tratando de ejecutar. Observe también que la emisión de un comando GO, descartará la @errorCode variable, y deben ser declarados en el código antes de ser usados (no está en el código de abajo).

BEGIN TRANSACTION

DECLARE @errorCode INT
SET @errorCode = @@ERROR

-- **********************************
-- * Settings
-- **********************************
IF @errorCode = 0
BEGIN
 BEGIN TRY
  ALTER TABLE Color ADD [CodeID] [uniqueidentifier] NOT NULL DEFAULT ('{00000000-0000-0000-0000-000000000000}')
  GO
 END TRY
 BEGIN CATCH
  SET @errorCode = @@ERROR
 END CATCH
END

IF @errorCode = 0
BEGIN
 BEGIN TRY
  UPDATE Color
  SET CodeID= 'B6D266DC-B305-4153-A7AB-9109962255FC'
  WHERE [Name] = 'Red'
 END TRY
 BEGIN CATCH
  SET @errorCode = @@ERROR
 END CATCH
END

-- **********************************
-- * Check @errorCode to issue a COMMIT or a ROLLBACK
-- **********************************
IF @errorCode = 0
BEGIN
 COMMIT
 PRINT 'Success'
END
ELSE 
BEGIN
 ROLLBACK
 PRINT 'Failure'
END

Así que lo que me gustaría saber es cómo ir alrededor de este problema, emitir sentencias ALTER TABLE para agregar una columna y luego actualizar esa columna, todo dentro de un script que se ejecuta como una unidad transaccional.

Author: abatishchev, 2010-12-14

6 answers

GO no es un comando T-SQL. Es un delimitador de lotes. La herramienta cliente (SSM, sqlcmd, osql, etc.) la usa para cortar el archivo en cada visita y enviar al servidor los lotes individuales. Así que, obviamente, no se puede utilizar IR dentro SI, ni se puede esperar variables para abarcar el ámbito a través de lotes.

Además, no puede capturar excepciones sin verificar la XACT_STATE() para asegurar que la transacción no está condenada.

Usar GUID para IDs siempre es al menos sospechoso.

Usar restricciones NOT NULL y proporcionar un 'guid' predeterminado como '{00000000-0000-0000-0000-000000000000}' tampoco puede ser correcto.

Actualizado:

  • Separe el ALTER y la ACTUALIZACIÓN en dos lotes.
  • Use extensiones sqlcmd para romper el script en caso de error. Esto es soportado por SSMS cuando el modo sqlcmd está en , sqlcmd, y es trivial soportarlo también en bibliotecas cliente: dbutilsqlcmd .
  • uso XACT_ABORT para forzar a error a interrumpir el lote. Este se utiliza con frecuencia en scripts de mantenimiento (cambios de esquema). Los procedimientos almacenados y los scripts lógicos de la aplicación en general usan bloques TRY-CATCH en su lugar, pero con el cuidado adecuado: Manejo de excepciones y transacciones anidadas.

Script de ejemplo:

:on error exit

set xact_abort on;
go

begin transaction;
go

if columnproperty(object_id('Code'), 'ColorId', 'AllowsNull') is null
begin
    alter table Code add ColorId uniqueidentifier null;
end
go

update Code 
  set ColorId = '...'
  where ...
go

commit;
go

Solo un script exitoso alcanzará el COMMIT. Cualquier error abortará el script y revertirá.

He utilizado COLUMNPROPERTY para comprobar la existencia de la columna, puede utilizar cualquier método que desee en su lugar (por ejemplo. búsqueda sys.columns).

 38
Author: Remus Rusanu,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-01-21 18:45:43

Ortogonal a los comentarios de Remus, lo que puede hacer es ejecutar la actualización en un sp_executesql.

ALTER TABLE [Table] ADD [Xyz] NVARCHAR(256);

DECLARE @sql NVARCHAR(2048) = 'UPDATE [Table] SET [Xyz] = ''abcd'';';
EXEC sys.sp_executesql @query = @sql;

Hemos tenido que hacer esto al crear scripts de actualización. Por lo general, solo usamos GO, pero ha sido necesario hacer las cosas condicionalmente.

 18
Author: Mark Sowul,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-11-05 15:20:58

Casi estoy de acuerdo con Remus pero puedes hacer esto con SET XACT_ABORT ON y XACT_STATE

Básicamente

  • SET XACT_ABORT ON abortará cada lote en caso de error y REVERSIÓN
  • Cada lote está separado por GO
  • La ejecución salta al siguiente lote en caso de error
  • Use XACT_STATE () comprobará si la transacción sigue siendo válida

Herramientas como Red Gate SQL Compare usan esta técnica

Algo como:

SET XACT_ABORT ON
GO
BEGIN TRANSACTION
GO

IF COLUMNPROPERTY(OBJECT_ID('Color'), 'CodeID', ColumnId) IS NULL
   ALTER TABLE Color ADD CodeID [uniqueidentifier] NULL
GO

IF XACT_STATE() = 1
  UPDATE Color
  SET CodeID= 'B6D266DC-B305-4153-A7AB-9109962255FC'
  WHERE [Name] = 'Red'
GO

IF XACT_STATE() = 1
 COMMIT TRAN
--else would be rolled back

También he eliminado el valor predeterminado. No value = NULL para los valores GUID. Está destinado a ser único: no intentes establecer cada fila a todos los ceros porque terminará en lágrimas...

 17
Author: gbn,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-12-14 20:19:09

¿Lo has probado sin el IR?

Normalmente no debe mezclar cambios de tabla y cambios de datos en el mismo script.

 2
Author: HLGEM,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-12-14 19:32:05

Otra alternativa, si no desea dividir el código en lotes separados, es usar EXEC para crear un ámbito/lote anidado como aquí

 1
Author: M.Sabaa,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 12:18:15

Creo que puede usar un ";" para terminar y ejecutar cada comando individual, en lugar de IR.

Tenga en cuenta que GO no es parte de Transact-SQL:

Http://msdn.microsoft.com/en-us/library/ms188037.aspx

 -1
Author: davek,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-12-14 19:34:59