SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 SQL Error: IF NOT EXIST.. CREATE VIEW
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bmassey
Starting Member

USA
22 Posts

Posted - 02/01/2008 :  16:55:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5946 Posts

Posted - 02/01/2008 :  17:01:29  Show Profile  Reply with Quote
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
Go to Top of Page

bmassey
Starting Member

USA
22 Posts

Posted - 02/01/2008 :  17:26:52  Show Profile  Reply with Quote
Thanks for the feedback TG!
Go to Top of Page

Van
Constraint Violating Yak Guru

458 Posts

Posted - 02/01/2008 :  17:31:07  Show Profile  Reply with Quote
What I've always done is:
if exists(.....)
drop object

create object
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5946 Posts

Posted - 02/01/2008 :  18:04:29  Show Profile  Reply with Quote
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
Go to Top of Page

bmassey
Starting Member

USA
22 Posts

Posted - 02/01/2008 :  20:42:24  Show Profile  Reply with Quote
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.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5946 Posts

Posted - 02/01/2008 :  20:57:51  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000