Very Computer

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

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

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

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

see reply below

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.