por - como eliminar un campo de una tabla en sql

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

Question

Estoy creando un script que se ejecutará en un servidor MS SQL. Este script ejecutará varios enunciados y debe ser transaccional; si uno de los enunciados falla, se detiene la ejecución general y se revierten los cambios.

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, uso un comando GO para ejecutar la instrucción ALTER TABLE y luego invoco mi instrucción UPDATE. 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 muestra del script que estoy tratando de ejecutar. También tenga en cuenta que al emitir un comando GO, descartará la variable @errorCode, y deberá declararse inactivo en el código antes de ser utilizado (esto no está en el código a continuación).

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

Entonces, lo que me gustaría saber es cómo solucionar 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.

Answer 1

¿Lo has probado sin GO?

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

Answer 2

Casi estoy de acuerdo con Remus, pero puedes hacerlo con SET XACT_ABORT ON y XACT_STATE

Básicamente

• SET XACT_ABORT ON abortará cada lote en caso de error y ROLLBACK
• Cada lote está separado por GO
• La ejecución salta al siguiente lote en caso de error
• Use XACT_STATE () probará si la transacción aún es 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 eliminé el valor predeterminado. Sin valor = NULL para valores GUID. Está destinado a ser único: no intentes configurar cada fila a todos los ceros porque terminará en lágrimas ...

Answer 3

Creo que puedes usar un ";" 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

Answer 4

GO no es un comando T-SQL. Es un delimitador de lote. La herramienta del cliente (SSM, sqlcmd, osql, etc.) la usa para cortar de manera efectiva el archivo en cada GO y enviar al servidor los lotes individuales. Entonces, obviamente, no se puede usar GO inside IF, ni se puede esperar que las variables cubran el alcance en los lotes.

Además, no puede detectar excepciones sin verificar el XACT_STATE() para asegurarse de que la transacción no está condenada.

El uso de GUID para ID siempre es al menos sospechoso.

El uso de restricciones NOT NULL y el suministro de un ''guid'' predeterminado como ''{00000000-0000-0000-0000-000000000000}'' tampoco pueden ser correctos.

Actualizado:

• Separe ALTER y UPDATE en dos lotes.
• Use extensiones sqlcmd para romper el script en caso de error. Esto es soportado por SSMS cuando el modo sqlcmd está activado, sqlcmd, y es trivial para soportarlo también en las bibliotecas cliente: dbutilsqlcmd .
• use XACT_ABORT para forzar el error para interrumpir el lote. Esto se usa con frecuencia en scripts de mantenimiento (cambios de esquema). Los procedimientos almacenados y los scripts lógicos de aplicación en general usan bloques TRY-CATCH en su lugar, pero con la debida precaución: 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 llegará al COMMIT . Cualquier error abortará la secuencia de comandos y la reversión.

Utilicé COLUMNPROPERTY para verificar la existencia de columna, podría usar cualquier método que sys.columns (por ejemplo, lookup sys.columns ).

Answer 5

Ortogonal a los comentarios de Remus, lo que puedes 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 necesitado hacer esto al crear scripts de actualización. Usualmente usamos GO pero ha sido necesario hacer cosas condicionalmente.

Answer 6

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