How do I conditionally create a table in Sybase (TSQL)?

OK, so Sybase (12.5.4) will let me do the following to DROP a table if it already exists:

IF EXISTS (
    SELECT 1
    FROM sysobjects
    WHERE name = 'a_table'
    AND type = 'U'
)
DROP TABLE a_table
GO

But if I try to do the same with table creation, I always get warned that the table already exists, because it went ahead and tried to create my table and ignored the conditional statement. Just try running the following statement twice, you'll see what I mean:

IF NOT EXISTS (
    SELECT 1
    FROM sysobjects
    WHERE name = 'a_table'
    AND type = 'U'
)
CREATE TABLE a_table (
    col1 int not null,
    col2 int null
)
GO

Running the above produces the following error:

*SQL Server Error on (localhost) Error:2714 at Line:7 Message:There is already an object named 'a_table' in the database.*

What's the deal with that?!

Answers


The only workaround I've come up with so far is to use execute immediate:

IF NOT EXISTS (
    SELECT 1
    FROM sysobjects
    WHERE name = 'a_table'
    AND type = 'U'
)
EXECUTE("CREATE TABLE a_table (
    col1 int not null,
    col2 int null
)")
GO

works like a charm, feels like a dirty hack.


Need Your Help

Parallel.ForEach() changes Impersonation Context

c# .net task-parallel-library impersonation parallel.foreach

Today we deployed our newly created ASP.NET application to the server and soon we realized there was a strange security-related issue which was causing the application to crash. This is an internal

AngularJS referencing associative array or object after returning from service

javascript arrays angularjs

I'm new to AngularJS (and primarily a PHP Dev) and am trying to pull data back from a service into my controller and then reference that array/object. I get the data back from $http fine and can

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.