Newbie question

Question

Newbie question

jon-645606

SSC Veteran

Points: 242
More actions
April 10, 2008 at 4:29 am

#186377

Hi.
I am having problems understanding the order in which SQL Commands are executed.
This came about when I wrote the following script.
If not exists(select * from sys.databases where name = 'IPPBxCdr')
Create Database IPPbxcdr
Go
If not exists(select * from IPPxCdr where name = 'Users')
CREATE TABLE [dbo].[Users](
[UserID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Users_UserID] DEFAULT (''),
[Password] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Users_Password] DEFAULT (''),
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Users_Name] DEFAULT (''),
[EMail] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Users_EMail] DEFAULT (''),
[AccountType] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
)
GO
Now I was under the impression that these commands were executed in a top to bottom sequence, so in other words I
a) first check to see if the database exists and if not creates it.
b) the check if the table exists and if not creates it in the database i just created.
Some reason I get an error message saying Invalid object name?
Anyone care to shed a light on what in the name of heebie jeebies im doing wrong?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

jon (4/10/2008)
Hi.
I am having problems understanding the order in which SQL Commands are executed.
This came about when I wrote the following script.
If not exists(select * from sys.databases where name = 'IPPBxCdr')
Create Database IPPbxcdr
Go
If not exists(select * from IPPxCdr where name = 'Users')
CREATE TABLE [dbo].[Users](
[UserID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Users_UserID] DEFAULT (''),
[Password] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Users_Password] DEFAULT (''),
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Users_Name] DEFAULT (''),
[EMail] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Users_EMail] DEFAULT (''),
[AccountType] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
)
GO
Now I was under the impression that these commands were executed in a top to bottom sequence, so in other words I
a) first check to see if the database exists and if not creates it.
b) the check if the table exists and if not creates it in the database i just created.
Some reason I get an error message saying Invalid object name?
Anyone care to shed a light on what in the name of heebie jeebies im doing wrong?

Hi Jon,

The first statement is right and that works fine. The second statement however has a couple of minor problems.

Firstly, you have to change the current database context to the new database you've just created. Otherwise, the statement will run (and potentially create) the table in the master database.

So after the first statement you'd want to issue a USE IPPBxCdr command.

The other issue lies with the IF statement - "If not exists(select * from IPPxCdr where name = 'Users')". In this statement the table IPPxCdr doesn't exist yet so the error message is just saying that IPPxCdr is an invalid object name. Instead, you should select from sys.tables. So your statement should look like:

If not exists(select * from sys.databases where name = 'IPPBxCdr')

create database IPPbxcdr;

go

use IPPbxcdr;

go

if not exists(select * from sys.tables where name = 'IPPxCdr' and type = 'U')

CREATE TABLE [dbo].[Users](

[UserID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Users_UserID] DEFAULT (''),

[Password] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Users_Password] DEFAULT (''),

[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Users_Name] DEFAULT (''),

[EMail] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Users_EMail] DEFAULT (''),

[AccountType] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

);

go

Karl
source control for SQL Server

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

There's no such thing as a stupid question.... 😀

The order of your statements is fine. The problem is your exists statement

If not exists(select * from IPPxCdr where name = 'Users')

I'm guessing your intention is to see if there's a table named users in the database that you just created - IPPbxCdr.

A select statement retrieves one or more rows from a table. The pbject referenced in the from clause must be a table or a view. You can't use a database in the from to see if a table exists.

The exists statement is checking for the existance of a row, not of an object.

What you want is something like this

If not exists(select * from sys.databases where name = 'IPPBxCdr')

Create Database IPPbxcdr

Go

USE IPPbxcdr -- change context to the database you've just created.

GO

If not exists(select 1 from sys.tables where name = 'Users')

-- sys.tables is a table that contains a list of all tables in the database

CREATE TABLE [dbo].[Users](

....

Does that help?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jon-645606 SSC Veteran Points: 242 More actions · Answer 3

😉

Doh! Of course.

Thanks a bunch guys , I can see the error now.