varsha2
asked on
Creating a table from a stored procedure
Hi
I am trying to create a table from a stored procedure, this table to be created is not in the temp db(basicaly it is a premenant table). I have the following questions
1. how do i check whether the object already exists in the database
2. If I use "create table" and if the table already exists then the procedure would not even compile. Is there a way I could execute a query inside a string, for example MSSQL allows to execute query inside a stored procedure using
Exec("select * from t1"), Is there an equivalent for this in sybase
Thanks
I am trying to create a table from a stored procedure, this table to be created is not in the temp db(basicaly it is a premenant table). I have the following questions
1. how do i check whether the object already exists in the database
2. If I use "create table" and if the table already exists then the procedure would not even compile. Is there a way I could execute a query inside a string, for example MSSQL allows to execute query inside a stored procedure using
Exec("select * from t1"), Is there an equivalent for this in sybase
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Check it from systable or Check the status of create table statement.
If u want to create table even it exists
use create existing table
----
The CREATE EXISTING TABLE statement creates a new local, proxy table that maps to a table at an external location. The CREATE EXISTING TABLE statement is a variant of the CREATE TABLE statement. The EXISTING keyword is used with CREATE TABLE to specify that a table already exists remotely and that its metadata is to be imported into Adaptive Server Anywhere. This establishes the remote table as a visible entity to Adaptive Server Anywhere users. Adaptive Server Anywhere verifies that the table exists at the external location before it creates the table.
-----
If u want to create table even it exists
use create existing table
----
The CREATE EXISTING TABLE statement creates a new local, proxy table that maps to a table at an external location. The CREATE EXISTING TABLE statement is a variant of the CREATE TABLE statement. The EXISTING keyword is used with CREATE TABLE to specify that a table already exists remotely and that its metadata is to be imported into Adaptive Server Anywhere. This establishes the remote table as a visible entity to Adaptive Server Anywhere users. Adaptive Server Anywhere verifies that the table exists at the external location before it creates the table.
-----
Don't use the create existing table syntax.
Create existing table creates a proxy table - a 'view' pointing to another database server. Every proxy table has to have a unique name among all tables (proxy or 'classic').
There are only two ways to check if the table exists - check the Object_id(object_name) function for null result or query the sysobject table for a table (type='U') named ... (name='...')
And as for the exec(string) function - the execute immediatelly is part o new Adaptive Server Enterprise 12.0 functionality, but you can achieve it even in 11.5.x or 11.9.x .
The high level steps are:
create stored procedure sp_exec with three string arguments - command, login, password
this stored procedure will do following:
using extended procedures it will create a file named script.sql containing the command string on the dbservers filesystem
using extended procedures it will execute isql command using the login and password suplied and input file script.sql
I didn't say it will be easy ;-)
Create existing table creates a proxy table - a 'view' pointing to another database server. Every proxy table has to have a unique name among all tables (proxy or 'classic').
There are only two ways to check if the table exists - check the Object_id(object_name) function for null result or query the sysobject table for a table (type='U') named ... (name='...')
And as for the exec(string) function - the execute immediatelly is part o new Adaptive Server Enterprise 12.0 functionality, but you can achieve it even in 11.5.x or 11.9.x .
The high level steps are:
create stored procedure sp_exec with three string arguments - command, login, password
this stored procedure will do following:
using extended procedures it will create a file named script.sql containing the command string on the dbservers filesystem
using extended procedures it will execute isql command using the login and password suplied and input file script.sql
I didn't say it will be easy ;-)
Don't use the create existing table syntax.
Create existing table creates a proxy table - a 'view' pointing to another database server. Every proxy table has to have a unique name among all tables (proxy or 'classic').
There are only two ways to check if the table exists - check the Object_id(object_name) function for null result or query the sysobject table for a table (type='U') named ... (name='...')
And as for the exec(string) function - the execute immediatelly is part o new Adaptive Server Enterprise 12.0 functionality, but you can achieve it even in 11.5.x or 11.9.x .
The high level steps are:
create stored procedure sp_exec with three string arguments - command, login, password
this stored procedure will do following:
using extended procedures it will create a file named script.sql containing the command string on the dbservers filesystem
using extended procedures it will execute isql command using the login and password suplied and input file script.sql
Maybe same behaviour is possible using RPC calls even in ASE 11.0.x ...
I didn't say it will be easy ;-)
Create existing table creates a proxy table - a 'view' pointing to another database server. Every proxy table has to have a unique name among all tables (proxy or 'classic').
There are only two ways to check if the table exists - check the Object_id(object_name) function for null result or query the sysobject table for a table (type='U') named ... (name='...')
And as for the exec(string) function - the execute immediatelly is part o new Adaptive Server Enterprise 12.0 functionality, but you can achieve it even in 11.5.x or 11.9.x .
The high level steps are:
create stored procedure sp_exec with three string arguments - command, login, password
this stored procedure will do following:
using extended procedures it will create a file named script.sql containing the command string on the dbservers filesystem
using extended procedures it will execute isql command using the login and password suplied and input file script.sql
Maybe same behaviour is possible using RPC calls even in ASE 11.0.x ...
I didn't say it will be easy ;-)
null
Be sure that U R not using begin tran for that procedure while executing it .because create table is not allowed if transaction level > 0
U can create table after checking that table existance of table in sysobjects cataloge of that database.
U can create table after checking that table existance of table in sysobjects cataloge of that database.
if not exists (select * from sysobjects where type = "U" and name = "XYZ")
begin
create table XYZ
( ... int,
....
)
end
All database objects in sybase (procedures, tables, views..) have an entry in a table called sysobjects.