stored procedure

stored procedure

Post by Carlos Gallard » Fri, 23 Mar 2001 00:45:49



Hello everybody

I need to know if there's any way of creating a stored procedure that also
creates authomatically another stored procedure including on it: drop proc,
insert record, and roolback checking, given a table name as an input to the
first procedure.

Thanks

 
 
 

stored procedure

Post by Don R. Watter » Fri, 23 Mar 2001 12:36:36


Hi Carlos,

    BOL states that you can't.  But, I'd be willing to bet that there are
ways around it.  For instance...just because you can't use the words in the
stored procedure doesn't mean you can't call an osql session using
xp_cmdshell, with the commands to run the osql command in one file and the
sql commands in another.  You can pass parameters too, so I would think you
can do it ... the question is, is it worth all of your time and effort.

Regards,

Don R. Watters
Database Admin\Developer
PhotoWorks, Inc.

P.S. Here's the text from BOL:

Rules for Programming Stored Procedures
Rules for programming stored procedures include:

  a.. The CREATE PROCEDURE definition itself can include any number and type
of SQL statements except for the following CREATE statements, which cannot
be used anywhere within a stored procedure: CREATE DEFAULT CREATE TRIGGER
        CREATE PROCEDURE CREATE VIEW
        CREATE RULE

  b.. Other database objects can be created within a stored procedure. You
can reference an object created in the same stored procedure as long as it
is created before it is referenced.

  c.. You can reference temporary tables within a stored procedure.

  d.. If you create a local temporary table inside a stored procedure, the
temporary table exists only for the purposes of the stored procedure; it
disappears when you exit the stored procedure.

  e.. If you execute a stored procedure that calls another stored procedure,
the called stored procedure can access all objects created by the first
stored procedure, including temporary tables.

  f.. If you execute a remote stored procedure that makes changes on a
remote instance of Microsoft? SQL ServerT 2000, those changes cannot be
rolled back. Remote stored procedures do not take part in transactions.

  g.. The maximum number of parameters in a stored procedure is 2100.

  h.. The maximum number of local variables in a stored procedure is limited
only by available memory.

  i.. Depending on available memory, the maximum size of a stored procedure
is 128 megabytes (MB).
For more information about the rules for creating stored procedures, see
CREATE PROCEDURE.


Quote:> Hello everybody

> I need to know if there's any way of creating a stored procedure that also
> creates authomatically another stored procedure including on it: drop
proc,
> insert record, and roolback checking, given a table name as an input to
the
> first procedure.

> Thanks


 
 
 

stored procedure

Post by Arthur Rakitski » Fri, 23 Mar 2001 21:30:54


You CAN do it in the next way : Use EXEC(_string_) statement to create your ne
SP.
Run this in QA, the result is two SP.

CREATE PROCEDURE tempo
AS

GO
EXEC tempo


> Hello everybody

> I need to know if there's any way of creating a stored procedure that also
> creates authomatically another stored procedure including on it: drop proc,
> insert record, and roolback checking, given a table name as an input to the
> first procedure.

> Thanks

 
 
 

stored procedure

Post by Arthur Rakitski » Fri, 23 Mar 2001 21:36:00


"... if You see on the cage with lion nameboard 'Elephant' - don`t trust Your
eyes"

see reply below


>     BOL states that you can't.

 
 
 

stored procedure

Post by Sinh » Sat, 24 Mar 2001 15:04:35


I faced some problems creating login to sql 7.0 database server using stored
procedure through application(c,sdk,odbc api based).
Is there any other way to create login to server apart from stored
procedure.
 
 
 

1. Calling a Java Stored Procedure from another Java Stored Stored Procedure

Hi,
I'm using the stored procedure builder of DB2 UDB v6.1 on NT to create a
Java Stored Procedure to call another Java Stored Procedure. Both of
them belong to the same project in stored procedure builder.
The sp that calls another sp has the code as follows:
// Calling another java sp -- ErrorHandler
ErrorHandler err = new ErrorHandler();
err.execute("Test #1", 100, "Testing #1");
When I want to build the sp which calls another sp from within, it gave
me an error as follows:
C:\IBMVJava\ide\tools\com-ibm-db2-tools-dev-spb-ivj\spb\bld953747549650\
com\intertrac\datamart\sp\SelectBillShipAddr.java:18: Class
com.intertrac.datamart.sp.ErrorHandler not found in type declaration.
ErrorHandler err = new ErrorHandler();
Does anyone have any clue of how this can be properly done ? Please let
me know.
Thanks,
ra

Sent via Deja.com http://www.deja.com/
Before you buy.

2. Please suggest a SCM tool for oracle products

3. Stored Procedure calling Stored Procedure

4. 8i & 9i SQL commands....

5. Executing a Stored Procedure in a Stored Procedure and selecting on the result

6. Any one can help me

7. Call a stored procedure from another stored procedure

8. Installing IngresII on Solaris 8 o/s

9. Calling a stored procedure with parameters from another stored procedure

10. using SQL stored procedure results in stored procedure

11. Using Resultset in Stored Procedure in another stored procedure

12. Stored Procedure using another Stored Procedure

13. Calling a Stored Procedure from a Stored Procedure