CODEWITHSUNDEEP
sqlsql-servert-sqltransactions
w0051977
w0051977

Reputation: 15807

Alter statement in a Transaction

I execute the code below:

use AdventureWorks2008R2
begin transaction
BEGIN
alter table HumanResources.Department add newcolumn int
update HumanResources.Department set newcolumn=1 where departmentid=1
END
commit

The error I get is:

Invalid column name 'newcolumn'.

Can ALTER statements be included in Transactions like this? If so, how can I prevent this error?

I have researched this online e.g. here. I have not found an answer to my specific question.

Upvotes: 2

Views: 3528

Answers (3)

usr
usr

Reputation: 171178

Aaron has explained everything already. Another alternative that works for ad-hoc scripts in SSMS is to insert the batch separator GO so that the script is sent as two parts to the server. This only works if it is valid to split the script in the first place (you can't split an IF body for example).

Upvotes: 1

AgentSQL
AgentSQL

Reputation: 2940

If you check the existence of column, then it should work. 

BEGIN TRANSACTION;

    IF COL_LENGTH('table_name', 'newcolumn') IS NULL
    BEGIN
        ALTER TABLE table_name ADD newcolumn INT;
    END

  EXEC sp_executesql N'UPDATE table_name
    SET newcolumn = 1 WHERE DepartmentID = 1;';

COMMIT TRANSACTION;

Upvotes: 0

anon
anon

Reputation:

Yes, you can include an ALTER in a transaction. The problem is that the parser validates the syntax for your UPDATE statement, and can't "see" that you are also performing an ALTER. One workaround is to use dynamic SQL, so that the parser doesn't inspect your syntax (and validate column names) until runtime, where the ALTER will have already happened:

BEGIN TRANSACTION;

  ALTER TABLE HumanResources.Department ADD newcolumn INT;

  EXEC sp_executesql N'UPDATE HumanResources.Department 
    SET newcolumn = 1 WHERE DepartmentID = 1;';

COMMIT TRANSACTION;

Note that indentation makes code blocks much more easily identifiable (and your BEGIN/END was superfluous).

Upvotes: 11

Related Questions