Add column to table and then update it inside transaction
Solution 1
GO is not a T-SQL command. Is a batch delimiter. The client tool (SSM, sqlcmd, osql etc) uses it to effectively cut the file at each GO and send to the server the individual batches. So obviously you cannot use GO inside IF, nor can you expect variables to span scope across batches.
Also, you cannot catch exceptions without checking for the XACT_STATE()
to ensure the transaction is not doomed.
Using GUIDs for IDs is always at least suspicious.
Using NOT NULL constraints and providing a default 'guid' like '{00000000-0000-0000-0000-000000000000}'
also cannot be correct.
Updated:
- Separate the ALTER and UPDATE into two batches.
- Use sqlcmd extensions to break the script on error. This is supported by SSMS when sqlcmd mode is on, sqlcmd, and is trivial to support it in client libraries too: dbutilsqlcmd.
- use
XACT_ABORT
to force error to interrupt the batch. This is frequently used in maintenance scripts (schema changes). Stored procedures and application logic scripts in general use TRY-CATCH blocks instead, but with proper care: Exception handling and nested transactions.
example script:
: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
Only a successful script will reach the COMMIT
. Any error will abort the script and rollback.
I used COLUMNPROPERTY
to check for column existance, you could use any method you like instead (eg. lookup sys.columns
).
Solution 2
Orthogonal to Remus's comments, what you can do is execute the update in an sp_executesql.
ALTER TABLE [Table] ADD [Xyz] NVARCHAR(256);
DECLARE @sql NVARCHAR(2048) = 'UPDATE [Table] SET [Xyz] = ''abcd'';';
EXEC sys.sp_executesql @query = @sql;
We've needed to do this when creating upgrade scripts. Usually we just use GO but it has been necessary to do things conditionally.
Solution 3
I almost agree with Remus but you can do this with SET XACT_ABORT ON and XACT_STATE
Basically
- SET XACT_ABORT ON will abort each batch on error and ROLLBACK
- Each batch is separated by GO
- Execution jumps to the next batch on error
- Use XACT_STATE() will test if the transaction is still valid
Tools like Red Gate SQL Compare use this technique
Something like:
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
I've also removed the default. No value = NULL for GUID values. It's meant to be unique: don't try and set every row to all zeros because it will end in tears...
Solution 4
Have you tried it without the GO?
Normally you should not mix table changes and data changes in the same script.
Solution 5
Another alternative, if you don't want to split the code into separate batches, is to use EXEC to create a nested scope/batch as here
Related videos on Youtube
Guillermo Gomez
Updated on July 05, 2022Comments
-
Guillermo Gomez almost 2 years
I am creating a script that will be run in a MS SQL server. This script will run multiple statements and needs to be transactional, if one of the statement fails the overall execution is stopped and any changes are rolled back.
I am having trouble creating this transactional model when issuing ALTER TABLE statements to add columns to a table and then updating the newly added column. In order to access the newly added column right away, I use a GO command to execute the ALTER TABLE statement, and then call my UPDATE statement. The problem I am facing is that I cannot issue a GO command inside an IF statement. The IF statement is important within my transactional model. This is a sample code of the script I am trying to run. Also notice that issuing a GO command, will discard the @errorCode variable, and will need to be declared down in the code before being used (This is not in the code below).
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
So what I would like to know is how to go around this problem, issuing ALTER TABLE statements to add a column and then updating that column, all within a script executing as a transactional unit.
-
Tim over 13 yearsYour database would be in an ambiguous or otherwise untenable state if only the DDL gets executed? You couldn't do the DDL first, and then your DML, catching any errors on the DML if the DDL had failed?
-
Guillermo Gomez over 13 yearsThanks Tim, you are right on target! Wish I could vote your comment as a solution too.
-
-
Guillermo Gomez over 13 yearsThanks for such quick response. I have already tried that, but it doesn't work. Issuing the UPDATE statement throws an error "Invalid column name 'ColorID'."
-
Guillermo Gomez over 13 yearsThanks for your response. I am thinking of splitting the script into two. One for DDL and one for DML.
-
Guillermo Gomez over 13 yearsI ended up splitting my script in two files, a DDL and a DML. That way I don't need GO after ALTER statements.
-
ZygD over 13 yearsNope, the error is related to batch and compilation. At parse time, CodeID does not exist. Nothing to do with statement terminators
-
ZygD over 13 years@ggomez: Use SET XACT_ABORT ON like, say, Red gate tools
-
Remus Rusanu over 13 yearsInteresting, use
XACT_STATE()
as a means to keep state across batches. Didn't know Red Gate does this. Automated generated code can be bulletproofed withIF XACT_STATE()
checks on every batch start, but I'm not sure I'd trust developers to keep this discipline for the entire script lifetime... That's why I prefer:on abort exit
, even though it relies on client tools to support it. -
ZygD over 13 years@Remus Rusanu: Red Gate uses SET XACT_ABORT ON only and temp tables to span GO. It's easier with XACT_STATE() for simplicity. You're right about developers discipline though. I note your update is along the same lines as my answer too
-
Guillermo Gomez over 13 yearsThanks gbn, I am including that!
-
caesay over 6 years
Using GUIDs for IDs is always at least suspicious.
It's the default for the PK column of Asp.Net Identity tables, why is it suspicious? -
Yepeekai about 5 yearsThis is great :) but don't use this with powershell invoke-sqlcmd. It will work fine if no errors, but if something fails, not everything will be rolled back and further batches will be executed !!!
-
Codure over 4 yearsThis is exactly what I needed. Thank you.
-
Asaad Mamoun over 2 yearsThank you very much. Saved my day