SQL Server Views - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Server Views

Author

Topic

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-06-18 : 12:10:36

Is SQL Server stupid or what?!

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'TEMPELEMENTGROUP')
DROP VIEW TEMPELEMENTGROUP

CREATE VIEW TEMPELEMENTGROUP
AS
SELECT DISTINCT PROPAY_NINO, ELEMENT_GROUP
FROM PEN_COMPONENT_STAGE1
GO

When I run the above, I get an error message saying that the VIEW should be the first statment in a query batch. I added a GO after the DROP VIEW line and it worked. Doesn't SQL realise that the first two lines merely drop an existing VIEW with the same name before re-creating it?!
And why does a VIEW need to be declared first in a query batch anyway?

Good Luck!

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-18 : 12:39:31

No...

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'TEMPELEMENTGROUP')
DROP VIEW TEMPELEMENTGROUP
GO
CREATE VIEW TEMPELEMENTGROUP
AS
SELECT DISTINCT PROPAY_NINO, ELEMENT_GROUP
FROM PEN_COMPONENT_STAGE1
GO

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-18 : 12:41:57

Also I would do:


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEMPELEMENTGROUP]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[TEMPELEMENTGROUP]
GO

Instead...

Brett

8-)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-06-18 : 13:21:52

quote:
Is SQL Server stupid or what?!

It's only as smart as the people using it. No offense.

quote:
Doesn't SQL realise that the first two lines merely drop an existing VIEW with the same name before re-creating it?!

See above.

quote:
And why does a VIEW need to be declared first in a query batch anyway?

Books Online will tell you, if you spend a moment reading it, rather than complaining about SQL Server's "stupidity".

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-18 : 14:44:07

Yeah, I hate when I do things like delete emails or files by accident and the stupid computer isn't smart enought to know what I *meant* to do!

stupid computers ....

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-18 : 15:04:10

quote:

It's only as smart as the people using it. No offense.

How did I know that was coming....esp with a simple post....

Sorry Amethystium

The more you know, the more you don't know

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-18 : 15:05:29

quote:

stupid computer isn't smart enought to know what I *meant* to do!

stupid computers ....

- Jeff

Yeah, now if I can just get my stupid computer to code for me, I'd be set.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources