Enclosing a table create inside BEGIN END

The following SQL (after the "The SQL" marker) works fine. But I want to enclose it in a check to make sure that a previous script hasn't already created this object and its CRUD.

If I enclose it in a block like this...

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ZIPCODE]') AND type in (N'U'))
BEGIN

blah blah blah

END

It won't parse. It seems to me like this should be a no brainer, but it is hurting my brain.

The SQL...

CREATE TABLE [dbo].[ZIPCODE](
[Zip] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Latitude] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Longitude] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[County] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip_class] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_ZIPCODE] PRIMARY KEY CLUSTERED
(
[Zip] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

/* Zip Code CRUD */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetZipLocationsWithinBounds]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetZipLocationsWithinBounds]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetZipLocationsByCityState]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetZipLocationsByCityState]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetZipLocation]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetZipLocation]
GO
CREATE PROCEDURE [dbo].[GetZipLocationsWithinBounds]
@.TopLine float,
@.LeftLine float,
@.Bottomline float,
@.RightLine float
AS
SELECT * FROM ZIPCODE
WHERE
LATITUDE >= @.Bottomline AND
LATITUDE <= @.TopLine AND
LONGITUDE >= @.LeftLine AND
LONGITUDE <= @.RightLine
ORDER BY State, City ASC
GO

CREATE PROCEDURE [dbo].[GetZipLocationsByCityState]
@.City varchar(25),
@.State varchar(2)
AS
SELECT * FROM ZIPCODE
WHERE
City = @.City AND
State = @.State
ORDER BY State, City ASC
GO

CREATE PROCEDURE [dbo].[GetZipLocation]
@.ZipCode varchar(10)
AS
SELECT * FROM ZIPCODE
WHERE
Zip = @.ZipCode
GO

Hi there,

This error is caused by a GO keyword. The GO command force the SQL Interpreter to execute the previous part of your code block! so if the Interpreter try to execute your first create command your IF block make this error.

If your statements doesn't have any dependency to each other your simply solve this problem by removing the GO commands; But if there is some dependency you must break them in an independent part.

Babak Izadi
LotraSoft Ltd.

|||

You should not use GO inside your T-SQL Batch statement.

Go is not a T-SQL Statement. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to SQL Server.

Remove all the GO statements & put it in your BEGIN .. END .. It will work ..