CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
MeteorMan
MeteorMan

Reputation: 57

Statement Execution order in SQL Server SQL Script

I am having trouble with a sql script that seems to be related to the execution order of the statements or possibly just error checking that sql server does prior to starting.

This is a simplification of the code but for background, the table finalTable is cleared and repopulated by this script. It already exists in the DB but does not have new columns that are being added. For reference in the example, pretend Col1 is an existing column and Col2 is new.

If I run the code all together, I get a message saying Invalid Column name 'Col2'. If I run each block individually, everything works fine.

Block A:

SQL to create temporary tables

Block B:

drop table dbo.finalTable;
create table dbo.finalTable (col1 int, col2 int);

Block C:

insert into dbo.finalTable(col1, col2) select col1, col2 from #tempTable;

Upvotes: 0

Views: 446

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46425

The script snippets you posted are not detailed enough to pinpoint the exact cause of the error but the symptoms suggest deferred name resolution. The issue is not statement execution order but compilation order.

SQL Server checks for syntax errors when a batch of statements is submitted for execution. If syntactically correct, statements referencing existing objects are validated against existing schema. Compilation of statements referencing non-existing objects are deferred until execution time. Deferred name resolution allows one to create a table and use it in the same batch:

CREATE TABLE dbo.finalTable (col1 int);
--Compilation of this statement is not done until execution time due to deferred name resolution
SELECT col1 FROM dbo.finaltable;
GO

Compilation of the entire batch will fail when a statement references a non-existing column of an existing table. No statements, including the first SELECT ALTER TABLE, are executed in this batch because col2 does not exist when the batch is compiled:

SELECT col1 FROM dbo.finaltable;
ALTER TABLE dbo.finaltable 
    ADD col2 int NULL;
SELECT col1, col2 FROM dbo.finaltable;
GO

Upvotes: 1

Wendy
Wendy

Reputation: 660

Is it the same database the script execution and finalTable exists when you executed the script as whole? You may try to 

 IF OBJECT_ID('databasename.dbo.finalTable', 'U') IS NOT NULL 
      DROP TABLE databasename.dbo.finalTable;

Upvotes: 0

Related Questions