SQL Error: IF NOT EXIST.. CREATE VIEW

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 Error: IF NOT EXIST.. CREATE VIEW

Author

Topic

bmassey
Starting Member

22 Posts

Posted - 2008-02-01 : 16:55:19

I am writing a SQL deployment script and I want to check to see if a VIEW exists. If not, then create the view. The logic and code seems pretty straight forward but SQL doesn't like it. The view statement runs fine when run by itself but not inside the if clause. Any assistance would be greatly appreciated.


IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'[dbo].[VW_MyViewName]') AND OBJECTPROPERTY(id, N'IsView') = 1)
BEGIN
	CREATE VIEW dbo.VW_MyViewName
	AS
	SELECT ....
END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-01 : 17:01:29

This is what I consider an annoying aspect of sql server. It is smart enough to try an validate your statements and check your syntax before the code is actually attempted, but it is not smart enough to realize the conditional nature of the CREATE statement. One solutition is to EXEC('') the CREATE:
exec('
create view ....
')

Be One with the Optimizer
TG

bmassey
Starting Member

22 Posts

Posted - 2008-02-01 : 17:26:52

Thanks for the feedback TG!

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-02-01 : 17:31:07

What I've always done is:
if exists(.....)
drop object

create object

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-01 : 18:04:29

quote:
Originally posted by Van

What I've always done is:
if exists(.....)
drop object

create object

same here. But you'll need a "GO" before the create statement. the CREATE needs to be the first statement in the batch.

Be One with the Optimizer
TG

bmassey
Starting Member

22 Posts

Posted - 2008-02-01 : 20:42:24

I've used the drop and create method in the past. However, our department is moving more towards role based security and dropping/re-creating objects also results in loss of permissions on the original object.

It's probably not as big of a concern for new objects but certainly can cause issue for updating objects by dropping and re-creating them.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-01 : 20:57:51

We include object level permissions in the same script (as the object). That would fit nicely for your "role based" security, one or two roles with uses as rolemembers.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources