Sybase if statement question?

[pmcmicha]

I need to verify if a table already exists or not in a certain database. If not, then the process will run, otherwise it will exit out. This is what I have, but it does not seem to be working:

if exists ( select 1 from sysobjects where name = 'DB_IN_QUESTION' )
begin
return
end
else
begin
DO_UPDATES_HERE
end

Regardless, it seems to go to the updates even if the table exists in sysobjects. I am not sure what I am doing incorrectly, could someone please help?

Sybase Version: 11.9.2 Adaptive Server

Thanks in advance.

 

[pmcmicha]

Nevermind, found out that this has something to do with my commands that I want processed. Since I am either creating a table or dropping one in either of the if statements I have, apparently Sybase reads ahead and performs those actions anyway regardless of the evaluation by the if statement.

 

[expostfacto]

that doesn't make sense to me. what tool are you using to execute this query? what happens when you do this?

if exists ( select 1 from sysobjects where name = 'DB_IN_QUESTION' )
begin
select 1
end
else
begin
select 2
end

http://www.carnageblender.com:

Carnage Blender. Over 40 million battles served.

 

[pmcmicha]

Tool: isql

I have two if statements, the solution to this problem would solve the issue for both, but I do not think that there is one other than what I already said. If I have this if statement:

if exists ( select 1 from sysobjects where name = 'DB' )
begin
print "Found It."
end
else
begin
print "Not Found."
end
GO

Then it works just fine, but if I introduce a drop or 'select into' statement, then it ignores the if statement even if it evaluates as true or false and performs the drop or 'select into' anyway.

if exists ( select 1 from sysobjects where name = 'DB' )
begin
print "Database already exists"
end
else
begin
select * into NEW_TABLE from OLD_TABLE where COL=xx
end
GO

For whatever reason, which I can only assume that Sybase is reading ahead and performing the action of creating a new table or dropping it in my other if statement. Perhaps it is isql, but someone else I was speaking with said that this was an issue with Sybase where they would read ahead.

Does this make more sense now or am I missing something?

 

[JeanNiBee]

You know what.

this may be abit off topic, but the other day I noticed I coulnd not create a stored proc because inside the procedure I had a command to DROP TABLE.

Even though allI was doing was CREATING the stored proc, not executing it, I would get the error "Cannot drop table 'tablename' because it does not exist int he systems catalog".

I knew it would not exist because it's a diffrent procedure that would create them. Plus, it was wrapped inside IF statements.

This is quite a pain if in fact Sybase reads ahead like that.

I'll have to do some detective work on this. -=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

TGML reference:

http://www.tipmaster.com/includes/tgmlinfo.cfm?w=450&h=450"

]

 

[grega]

I'm extremely puzzled at the "if" problem as I'm unable to replicate it in my 11.9.2 environment. I just used the code (slightly different to the original)

if object_id('table_which_does_exist') > 0
begin
   select "object exists"
end
else
begin
   select "OOPS!" oops into oops
end
go

Sorry I can't be of more help. Is there a DB option somewhere which controls this behaviour?

Greg.