Conditional Statements

Conditional Statements

Post by CB » Wed, 13 Nov 2002 15:17:47



Hi
  I am porting an application to DB2 which is now working on MSSQL
server. They have used conditional statements to check whether a table
already exists before trying to create a table. Something like this:

------
if not exists (select * from sysobjects where id =
object_id(N'testtable') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
CREATE TABLE ...
...
end
------

I tried to use the same logic in DB2 but it reported errors. When I
posted the error to ibm newsgroup I was told by Mr. Dirk that DDLs are
not allowed with in dynamic statements, so I cannot use in this way.
So how do I implement the same in the case of DB2.

thanks in advance,
dw

 
 
 

Conditional Statements

Post by Serge Riela » Wed, 13 Nov 2002 22:11:41


You can either write this logic in a stored procedure, or simply create
the table in the app and catch the "object does already exist" error. In
fact I believe that risking the error is going to be faster than first
checking the catalogs.

Cheers
Serge

--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Toronto

Visit DB2 Developer Domain at
http://www7b.software.ibm.com/dmdd/