Assisistance with an IF NOT Exists

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 2008 Forums

Transact-SQL (2008)

Assisistance with an IF NOT Exists

Author

Topic

MikeMiller
Starting Member

7 Posts

Posted - 2012-02-15 : 09:36:01

Hello-

First time poster here seeking a little assistance. I'm having what of an issue trying to create a view IF NOT EXIST and if so PRINT a friendly error instead of the hard SQL error 'view already exixts'. Below is the code I'm attempting to use. When I have the code in SSMS I get an "Incorrect syntax: Create View must be the only statement in the batch" when I hover over the CREATE VIEW statement. I'm been looking at this pretty much all morning, does anyone see what's missing here? THANKS MUCH!

USE DATABASE1;
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='aac_contract_earnings_history')
BEGIN

SET ANSI_NULLS ON;

SET QUOTED_IDENTIFIER ON;

CREATE VIEW [dbo].[aac_contract_earnings_history] AS

SELECT icontract_earnings_id, icontract_id, iearn_set_id, dtearnings_date, dtearnings_cycle_month, cearned_admin_1, cearned_admin_2,
cearned_admin_3, cearned_admin_4, cearned_admin_5, cearned_admin_6, cearned_admin_7, cearned_admin_8, cearned_admin_9, cearned_admin_10,
cearned_prem_1, cearned_prem_2, cearned_prem_3, cearned_prem_4, ctotal_earned_admin, ctotal_earned_prem, dtlast_record_maintenance,
[suser_id], supdate_user_id, iupdate_userid_type, dtupdate_last
FROM DATABASE2.dbo.aac_contract_earnings_history;
END

ELSE
BEGIN
PRINT 'View already exist'
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-15 : 09:43:52

try like


USE DATABASE1;
GO
SET ANSI_NULLS ON;

SET QUOTED_IDENTIFIER ON;

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='aac_contract_earnings_history')
 DROP VIEW aac_contract_earnings_history'
GO

CREATE VIEW [dbo].[aac_contract_earnings_history] AS



SELECT icontract_earnings_id, icontract_id, iearn_set_id, dtearnings_date, dtearnings_cycle_month, cearned_admin_1, cearned_admin_2,
cearned_admin_3, cearned_admin_4, cearned_admin_5, cearned_admin_6, cearned_admin_7, cearned_admin_8, cearned_admin_9, cearned_admin_10, 
cearned_prem_1, cearned_prem_2, cearned_prem_3, cearned_prem_4, ctotal_earned_admin, ctotal_earned_prem, dtlast_record_maintenance,
[suser_id], supdate_user_id, iupdate_userid_type, dtupdate_last
FROM DATABASE2.dbo.aac_contract_earnings_history;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MikeMiller
Starting Member

7 Posts

Posted - 2012-02-15 : 09:45:41

Thanks but I don't want to drop it if it exists, I only want to create it if it doesn't.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-15 : 09:46:20

see here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96633

No, you're never too old to Yak'n'Roll if you're too young to die.

MikeMiller
Starting Member

7 Posts

Posted - 2012-02-15 : 09:50:40

quote:
Originally posted by webfred

see here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96633

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks but I found that earlier and it gets the same results. 'Incorrect Syatax....etc'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-15 : 09:56:24

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='aac_contract_earnings_history')
BEGIN

SET ANSI_NULLS ON;

SET QUOTED_IDENTIFIER ON;
exec('
CREATE VIEW [dbo].[aac_contract_earnings_history] AS

SELECT icontract_earnings_id, icontract_id, iearn_set_id, dtearnings_date, dtearnings_cycle_month, cearned_admin_1, cearned_admin_2,
cearned_admin_3, cearned_admin_4, cearned_admin_5, cearned_admin_6, cearned_admin_7, cearned_admin_8, cearned_admin_9, cearned_admin_10,
cearned_prem_1, cearned_prem_2, cearned_prem_3, cearned_prem_4, ctotal_earned_admin, ctotal_earned_prem, dtlast_record_maintenance,
[suser_id], supdate_user_id, iupdate_userid_type, dtupdate_last
FROM DATABASE2.dbo.aac_contract_earnings_history;
')
END

ELSE
BEGIN
PRINT 'View already exist'
END

No, you're never too old to Yak'n'Roll if you're too young to die.

MikeMiller
Starting Member

7 Posts

Posted - 2012-02-15 : 10:06:45

quote:
Originally posted by webfred

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME='aac_contract_earnings_history')
BEGIN

SET ANSI_NULLS ON;

SET QUOTED_IDENTIFIER ON;
exec('
CREATE VIEW [dbo].[aac_contract_earnings_history] AS

SELECT icontract_earnings_id, icontract_id, iearn_set_id, dtearnings_date, dtearnings_cycle_month, cearned_admin_1, cearned_admin_2,
cearned_admin_3, cearned_admin_4, cearned_admin_5, cearned_admin_6, cearned_admin_7, cearned_admin_8, cearned_admin_9, cearned_admin_10,
cearned_prem_1, cearned_prem_2, cearned_prem_3, cearned_prem_4, ctotal_earned_admin, ctotal_earned_prem, dtlast_record_maintenance,
[suser_id], supdate_user_id, iupdate_userid_type, dtupdate_last
FROM DATABASE2.dbo.aac_contract_earnings_history;
')
END

ELSE
BEGIN
PRINT 'View already exist'
END

No, you're never too old to Yak'n'Roll if you're too young to die.