Batch on sql server

Sql server will compile all sql statements before ‘GO’ into a batch.

It will give you error if you run the sql below without ‘GO’ in between.

ALTER TABLE TORDERAUDIT ADD LAST_MODIFIED_DATE DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
UPDATE TORDERAUDIT SET LAST_MODIFIED_DATE = CURRENT_TIMESTAMP;

When sql server compile the second statement, it cannot find the new column on that table.  Here is the error info: “Invalid column name 'LAST_MODIFIED_DATE'.”

One way to solve it is to put ’GO’ in between. But it fails our dbfit tests.

Another way to solve it is to put the UPDATE statement into EXEC. Sql server will not compile anything in EXEC until it gets to be executed. Now it looks like

ALTER TABLE TORDERAUDIT ADD LAST_MODIFIED_DATE DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL;
EXEC ('UPDATE TORDERAUDIT SET LAST_MODIFIED_DATE = CURRENT_TIMESTAMP');

If you already have a single quotation in UPDATE statement, to escape it, put another ‘ before the single quotation.

EXEC ('UPDATE TORDERSKU SET GUID = ''GUID'' + RTRIM(CONVERT(char(22), UIDPK))');