Labels

Separate DDL and DML in Transact-SQL with Batches

I wrote about this problem in a Stackoverflow.com post called Problem with alter then update in try catch with tran using Transact-SQL where Mikael Eriksson gave me just the hint I needed. My problem came about when I tried to run the below Transact-SQL via sqlcmd (SQL Server 2005) (using the command: sqlcmd -S "localhost\SQLEXPRESS" -U sa -P password -i "C:\path\to\transform.sql").

USE PUK;
BEGIN TRANSACTION;
BEGIN TRY

   -- Modify the table RETRIEVAL_STAT
   alter table dbo.RETRIEVAL_STAT add
      SOURCE nvarchar(10) NULL,
      ACCOUNTNUMBER nvarchar(50) NULL,
      PUK nvarchar(20) NULL;

   -- Modify data in new columns of table RETRIEVAL_STAT
   update dbo.RETRIEVAL_STAT set
      SOURCE = 'XX',
      ACCOUNTNUMBER = 'XX',
      PUK = 'XX';

END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

Note that it contains code that alters a table (DDL) followed by code that modifies data in a table (DML). Also note that the DML is meant to work on the new columns introduced by the DDL. This code generates the below error.

(0 rows affected)
Changed database context to 'PUK'.
Msg 207, Level 16, State 1, Server localhost\SQLEXPRESS, Line 13
Invalid column name 'SOURCE'.
Msg 207, Level 16, State 1, Server localhost\SQLEXPRESS, Line 13
Invalid column name 'ACCOUNTNUMBER'.
Msg 207, Level 16, State 1, Server localhost\SQLEXPRESS, Line 13
Invalid column name 'PUK'.

The problem is that Transact-SQL is compiled first, then executed. It means that when the compiler goes through my SQL, it finds that the second block - the SQL that modifies the new columns - cannot possibly work because those columns don't exist yet. (Because it hasn't executed the first block yet, it is still compiling the whole lot!) The solution is simple enough. Place the blocks in separate batches, which are compiled (and thus executed) separately, but still in order because I will still place the batches in the same file, one after the other, and run them with the same command. A batch is a series of Transact-SQL statements (that may include a try-catch) separated by the GO command.

This code works.

USE PUK;
BEGIN TRANSACTION;
BEGIN TRY

   -- Modify the table RETRIEVAL_STAT
   alter table dbo.RETRIEVAL_STAT add
      SOURCE nvarchar(10) NULL,
      ACCOUNTNUMBER nvarchar(50) NULL,
      PUK nvarchar(20) NULL;

END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

USE PUK;
BEGIN TRANSACTION;
BEGIN TRY

   -- Modify data in new columns of table RETRIEVAL_STAT
   update dbo.RETRIEVAL_STAT set
      SOURCE = 'XX',
      ACCOUNTNUMBER = 'XX',
      PUK = 'XX';

END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

Pages that helped me with this post.

Labels:

Popular Posts