Dodgy use of GO - follow up

Subramanyam Krishnamurthy has posted about the use of GO in scripts and how it can be dodgy. Many people have provided feedback on the forum I though I could add mine.

A couple of points, just to confirm that GO isn't TSQL but a terminator used by DMO and SMO to parse batches of SQL. Whats more with SQLCMD you can put a number after the GO for the preceeding TSQL batch to be repeated.

The biggest issue I have seen is the deployment of sprocs. The easiest way is to concatenate all the files for the store procs together and run that combined file. However if one file doesn't have a go at the end and all the files start with a if exists drop statement you can end up with.

If exists (select 1 ....)

    drop proc myfirstProc

go

create proc myfirstProc

    begin

    --    do some stuff

    end

 

If exists (select 1 ....)

    drop proc mysecondProc

go

create proc mysecondProc

    begin

    -- Do some stuff

    end

go

If exists (select 1 ....)

    drop proc mythirdProc

go

create proc mythirdProc

    begin

    -- Do some stuff

    end 

What you find is that this is likely to run in, however the first time someone runs myFirstProc it will drop mysecondProc :( Not good.

On final note for those running scripts on SQL 2005 its a must that you start the script with :on error exit. This will avoid the problem detailed above, where one statement fails and but all the others are still run

-
Published Tuesday, February 21, 2006 5:18 PM by simonsabin

Comments

No Comments