Creating a Stored Procedure with a Stored Procedure

Hi all,
I was wonder if any one knows if its possible to create run and drop
a stored procedure from within another stored procedure. You see i want
to have a table which has serveral stored procedures stored in it as
strings then use a stored procedure to extract one of these and create
a stored procedure with it, run it and drop it when its done. Is this
possible, and if so how bad a performance can i expect (i'm expecting
pretty bad).I think you can do this. but you can't create sps in other db from another
dbs stored procedure|||Hi DigitalDiamond ,
Prehaps this link solve your problem
http://blog.csdn.net/lihonggen0/arc...8/15/75447.aspx
with warm regards
Jatinder Singh|||Think about the reasons for trying to achieve something like this. Generally
there are several better ways to do it.
1) create all needed procedures in advance, then use the first procedure to
select the appropriate one;
2) use the first procedure to dynamically build a query to execute;
3) if you're trying to hide your code from the rest of the database users,
use encryption.
What exactly are you trying to do?
ML|||On 26 Jul 2005 04:35:12 -0700, DigitalDiamond wrote:

>Hi all,
> I was wonder if any one knows if its possible to create run and drop
>a stored procedure from within another stored procedure. You see i want
>to have a table which has serveral stored procedures stored in it as
>strings then use a stored procedure to extract one of these and create
>a stored procedure with it, run it and drop it when its done. Is this
>possible, and if so how bad a performance can i expect (i'm expecting
>pretty bad).
Hi DigitalDiamond,
Why would you even *want* to do this? Why not just create all the stored
procedures, then execute the one you need?
Your idea is terribly bad. Not because of performance (that would
probably not be as bad as you expect), but because of security. It is
like going on vacation and leaving the door of your house not only
unlocked, but even wide open. And placing a big sign in the yard: "Hey,
burglars - the door is open and I'm away until the end of the month, so
please help yoourself!"
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Sure, you can always write horrible code in nay language! Have you
ever had a course in basic Software Engineering? Please take one. Pay
attentation to the idea of a module of code, coupling and cohesion.|||You can. You can create temporary stored procedures too. Should you?
Probably not. A better solution is to use sp_executesql to execute dynamic
queries. sp_executesql accepts both input and output parameters (see
kb:262499). It also doesn't mess around with the system tables.
"DigitalDiamond" <chris.diamond@.ncumbria.nhs.uk> wrote in message
news:1122377712.694590.77220@.g47g2000cwa.googlegroups.com...
> Hi all,
> I was wonder if any one knows if its possible to create run and drop
> a stored procedure from within another stored procedure. You see i want
> to have a table which has serveral stored procedures stored in it as
> strings then use a stored procedure to extract one of these and create
> a stored procedure with it, run it and drop it when its done. Is this
> possible, and if so how bad a performance can i expect (i'm expecting
> pretty bad).
>|||It doesnt matter now i found a better way of doing it via restructing
the database set up i had. And also i have a degree in software
enginerring Celko and if you want to be a arse dont do it of groups
designed to help people|||Why would anyone allow 100,000 lines of codes but not 1000 stored
procedures? Having a good practice in place can help manage and
maintain the systems. It would be hard to manage something that
virtually does not exist!!!
Perhaps there might be programs that would drop other programs on the
fly out there. To the extent that without any trail and therefore,
without our knowledge. This makes things really complicated.