Exec Stored Procedure

Hello

Which is faster :

to write a a big stored procedure with if conditions, or to separate them and call them using exec??

i.e:

if @.id=1
insert into ...
else if @.id=2
update...
--------
or
if @.id=1
exec InsertProcedure
else if @.id=2
exec UpdateProcedure

Please help

Its very difficult to jugde that which is faster inLine Query or Store procedure

Read What One of Microsoft Press books says about Store procedure

"

Stored procedures are executable server-side routines. They give you great power and performance benefits if used wisely. Unlike user-defined functions (UDFs), stored procedures are allowed to have side effects. That is, they are allowed to change data in tables, and even the schema of objects. Stored procedures can be used as a security layer. You can control access to objects by granting execution permissions on stored procedures and not to underlying objects. You can perform input validation in stored procedures, and you can use stored procedures to allow activities only if they make sense as a whole unit, as opposed to allowing users to perform activities directly against objects.

Stored procedures also give you the benefits of encapsulation; if you need to change the implementation of a stored procedure because you developed a more efficient way to achieve a task, you can issue an ALTER PROCEDURE statement. As long as the procedure's interface remains the same, the users and the applications are not affected. On the other hand, if you implement your business logic in the client application, the impact of a change can be very painful.

Stored procedures also provide many important performance benefits. By default, a stored procedure will reuse a previously cached execution plan, saving the CPU resources and the time it takes to parse, resolve, and optimize your code. Network traffic is minimized by shortening the code strings that the client submits to Microsoft SQL Server—the client submits only the stored procedure's name and its arguments, as opposed to the full code. Moreover, all the activity is performed at the server, avoiding multiple roundtrips between the client and the server. The stored procedure will pass only the final result to the client through the network"

And About User defined Funciton Or Say inline Query Statement

User-defined functions (UDFs) are routines that perform calculations/computations and return a value—scalar (singular) or table. Microsoft SQL Server 2000 introduced UDFs, which you developed with T-SQL and could incorporate in queries, computed columns, and constraints.

|||

probably the big stored procedure with everything in it will be faster but only by a matter of milliseconds. I would choose which ever option you will find easier to maintain and develop.

|||

Are there any clues or proves ??

Please I need that urgenlty

|||

It depends on what you are doing in the IF Loops. If you are just doing INSERTs one big proc should be fine, as far as performance is concerned. If you split it into multiple smaller procs, you have the ability to call individual proc if you need to reuse the INSERT to one of the tables rather than call the giant proc.

|||

If I split the big Procedure to small procedures, how much will performance be affected??
and what about execution plan??

Thank you

|||

In case of splitting sp , will SQL server builds and stores execution plans for exec called procedures??

Plz Advice

|||

JRICE:

If I split the big Procedure to small procedures, how much will performance be affected??
and what about execution plan??

Please re-read my earlier reply.

|||

Thanks for your reply,

I need an advice if Im concern about performance and I need reusability of my stored procedures, will it differ that much?

thank you in advance