TSQL - How to use GO inside of a BEGIN .. END block?
I had the same problem and finally managed to solve it using SET NOEXEC.
IF not whatever
BEGIN
SET NOEXEC ON;
END
ALTER TABLE dbo.EMPLOYEE ADD COLUMN EMP_IS_ADMIN BIT NOT NULL
GO
UPDATE dbo.EMPLOYEE SET EMP_IS_ADMIN = whatever
SET NOEXEC OFF;
You could try sp_executesql
, splitting the contents between each GO
statement into a separate string to be executed, as demonstrated in the example below. Also, there is a @statementNo variable to track which statement is being executed for easy debugging where an exception occurred. The line numbers will be relative to the beginning of the relevant statement number that caused the error.
BEGIN TRAN
DECLARE @statementNo INT
BEGIN TRY
IF 1=1
BEGIN
SET @statementNo = 1
EXEC sp_executesql
N' ALTER TABLE dbo.EMPLOYEE
ADD COLUMN EMP_IS_ADMIN BIT NOT NULL'
SET @statementNo = 2
EXEC sp_executesql
N' UPDATE dbo.EMPLOYEE
SET EMP_IS_ADMIN = 1'
SET @statementNo = 3
EXEC sp_executesql
N' UPDATE dbo.EMPLOYEE
SET EMP_IS_ADMIN = 1x'
END
END TRY
BEGIN CATCH
PRINT 'Error occurred on line ' + cast(ERROR_LINE() as varchar(10))
+ ' of ' + 'statement # ' + cast(@statementNo as varchar(10))
+ ': ' + ERROR_MESSAGE()
-- error occurred, so rollback the transaction
ROLLBACK
END CATCH
-- if we were successful, we should still have a transaction, so commit it
IF @@TRANCOUNT > 0
COMMIT
You can also easily execute multi-line statements, as demonstrated in the example above, by simply wrapping them in single quotes ('
). Don't forget to escape any single quotes contained inside the string with a double single-quote (''
) when generating the scripts.
You can enclose the statements in BEGIN and END instead of the GO inbetween
IF COL_LENGTH('Employees','EMP_IS_ADMIN') IS NULL --Column does not exist
BEGIN
BEGIN
ALTER TABLE dbo.Employees ADD EMP_IS_ADMIN BIT
END
BEGIN
UPDATE EMPLOYEES SET EMP_IS_ADMIN = 0
END
END
(Tested on Northwind database)
Edit: (Probably tested on SQL2012)
GO
is not SQL - it is simply a batch separator used in some MS SQL tools.
If you don't use that, you need to ensure the statements are executed separately - either in different batches or by using dynamic SQL for the population (thanks @gbn):
IF whatever
BEGIN
ALTER TABLE dbo.EMPLOYEE ADD COLUMN EMP_IS_ADMIN BIT NOT NULL;
EXEC ('UPDATE dbo.EMPLOYEE SET EMP_IS_ADMIN = whatever')
END