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.
Don R. Watters
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
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
> 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
> insert record, and roolback checking, given a table name as an input to
> first procedure.