Nested Stored Proc

Pleaseet me know the Advantages and Divantages of nested Stored porc in
sql server 2000.Mustafa
You have to understand why you need to use nested stored procedures
In my opinion one of "diventages" is a maintenance, that if one of the
DML is failed to rollback whole batch , so obviously it depends in your
business reqirements
Also read up this article
http://www.sql-server-performance.c..._procedures.asp
"Mustafa" <Mustafa@.discussions.microsoft.com> wrote in message
news:834A3DFF-CBA5-45B9-B798-DE41EC54E871@.microsoft.com...
> Pleaseet me know the Advantages and Divantages of nested Stored porc in
> sql server 2000.|||if by "nesting" you mean calling one SP from another, this is busy subject..
.
See Code Complete, or Code Complete II, by Steve McConnell, for basic ideas.
1. In general, Nesting allows you to better organize, resuse and maintain
the code you write.
2. Portions of code that are used by many SPs within adatabase system can
be written in a separate SP, and maintained in only one spot insteadof havin
g
to be copied and pasted in multiple places.
3. When you write a long SP that executes a different portion of it's code
each time it runs, depending on the values of parameters, the query optimize
r
and compiler will only optimize it for the portion of the code that it ran
the first time it ran - when it was compiled.. Subsequent runs, (unless it i
s
recompiled each time) it may not be running the best possible query plan.
4. There will many more SPs within you system to maintain if you split them
up into Main SPs and Subroutines.
5. Error handling in"Called" Subroutine SPs is a bit trickier. If you're
not familiar, make sure you read up on this topic...
etc.
"Mustafa" wrote:

> Pleaseet me know the Advantages and Divantages of nested Stored porc in
> sql server 2000.