CODEWITHSUNDEEP
sql-serverstored-procedures
MarkB
MarkB

Reputation: 45

SQL script which only runs after the addition of 'GO' command

I know lots have already been written about what GO does and when to use it but I haven't seen anything which explains the following scenario.

We have been supplied with some stored procedures with an application to populate a set of static warehouse tables, but they were producing error messages. Each procedure has the following structure:

exec usp_CreateDWTable

insert into dbo.DWTable
Select [somefields]
from [SomeLiveTables]

The usp_CreateDWTable stored procedure consists of:

if exists (select * from sysobjects where id = object_id('dbo.DWTable') and sysstat & 0xf = 3)
drop table dbo.DWTable

CREATE TABLE dbo.DWTable(CONSTRAINT [field1] PRIMARY KEY (Alert_ID),[field2],,,)

Running as above returns the error message: Msg 213, Level 16, State 1, Line 15 Column name or number of supplied values does not match table definition.

I have eventually worked out that adding GO between the EXEC and the INSERT fixes the issue but if sql process commands sequentially anyway what difference does this make? Now I know a CREATE TABLE batch has to be sent to the server before you can send an INSERT but I have read that sql can handle this scenario implicitly, is the fact that the CREATE TABLE is in a sp interfering with this?

Even if my above assumption above is correct, an additional source of confusion is that we have two instances of this database on the server and this error does not happen on the other database. The structures of both databases are virtually identical, although the data is different. Is there a setting within the database that is causing them to behave differently?

Thanks

Upvotes: 0

Views: 41

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46261

The entire batch is compiled using the existing table definition when the table already exists. So when the batch includes drop/create/insert, the batch is validated against the existing schema before the new table is created and compilation fails when the existing/new table has a different number of columns.

When you execute the script as separate batches (GO command), the drop/create statements are executed first. Then the insert statement batch is then compiled and run using the new table definition.

Upvotes: 2

Related Questions