SQL 2008 vs 2005 If Exists - 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 2012 Forums

Transact-SQL (2012)

SQL 2008 vs 2005 If Exists

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-03-18 : 12:45:57

Good afternoon,
I have a query that generates a table and its fields that works in 2005. Now I am on a 2008 Server and the query no longer works.

Was working code:

SELECT NCOS
USE

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.NCOS_Data') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.NCOS_Data
GO

CREATE TABLE dbo.NCOS_Data (
NC_UniqueID int identity(1,1),
NC_Lead_Number varchar (50)
)
GO

Now I get this error in 2008

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'if'.

Bryan Holmstrom

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-18 : 12:55:25

SELECT NCOS
USE

is invalid

is NCOS db name? if yes, it should be


USE NCOS
GO

..

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

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-03-18 : 13:03:38

Monday brains ugg

Thanks

Bryan Holmstrom

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-18 : 13:07:49

welcome

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

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2013-03-18 : 13:16:55

You can also use the object_id function instead of exists:

IF object_id('dbo.NCOS_Data', 'U') IS NOT NULL
DROP TABLE dbo.NCOS_Data;
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources