Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Creating a table from a stored procedure

Posted on 1999-10-14
7
Medium Priority
?
838 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:varsha2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 9

Accepted Solution

by:
david_levine earned 100 total points
ID: 2128253
Try the following in your stored proc:

IF OBJECT_ID('dbo.table_name') IS NOT NULL
    PRINT '<<< TABLE dbo.table_name EXISTS >>>'
ELSE
    PRINT '<<< TABLE dbo.table_name DOES NOT EXIST >>>'

Now, in the case of a table existing already and failing on a create table, how about having the create table as a separate stored proc and then in the main proc:

IF OBJECT_ID('dbo.table_name') IS NULL
    exec sp_create_missing_table

David
0
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 2320209
I think the same query can be written in a simpler fashion :

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.

0
 

Expert Comment

by:nagabalaji
ID: 2352340
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.
-----
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 2

Expert Comment

by:jkotek
ID: 2482426
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 ;-)
0
 
LVL 2

Expert Comment

by:jkotek
ID: 2482433
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 ;-)
0
 
LVL 1

Expert Comment

by:zhanghl
ID: 2514637
null
0
 

Expert Comment

by:pankajs020700
ID: 2521812
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.
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

While opting for any web-to-print solution, you need to discuss with your team and some of your end users and know their opinions about your decisions. In this article we list down some questions you need to ask yourself.
The core idea of this article is to make you acquainted with the best way in which you can export Exchange mailbox to PST format.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

664 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question