Help with reuse methology?

I find that many stored procedures are necessary in nearly all databases I
use. Creating dynamic (one that takes a string and executes the sql in it)
stored procedures is expensive performance wise. However, they do allow you
to pass in strings of sql and execute it. I find in general that SPs are
created and can only be used for one specific thing - registration form
input, account info update, etc. For example, I can't take the account
update SP and use it for the registration input SP.
Is there some general or fundamental guide that should be followed to lower
the overall number of stored procedures in a database?
Thanks,
BrettBrett wrote:
> I find that many stored procedures are necessary in nearly all
> databases I use. Creating dynamic (one that takes a string and
> executes the sql in it) stored procedures is expensive performance
> wise. However, they do allow you to pass in strings of sql and
> execute it. I find in general that SPs are created and can only be
> used for one specific thing - registration form input, account info
> update, etc. For example, I can't take the account update SP and use
> it for the registration input SP.
> Is there some general or fundamental guide that should be followed to
> lower the overall number of stored procedures in a database?
> Thanks,
> Brett
Not really. You need what you need. Dynamic SQL is not the way to go.
It's really no different than executing the SQL statement directly from
the client and exposes your database to SQL injection and performance
issues.
Writing stored procedures is just a part of the application development
process. You can leverage things like functions or utility-type stored
procs to simplify repetitive code, but not much you can do to lower the
number of procedures you need.
David Gugick
Imceda Software
www.imceda.com