• Eugene Elutin (3/11/2013)


    fmuzul (3/11/2013)


    My suggestion is: avoid stored procedures at all, unless you have very special needs of performance on some complicated operations.

    ...

    Calling statement! :w00t:

    I'm afraid you will not find many SQL specialists agreeing with the above...

    Actually first few paragraphs from BOL tell well enough:

    Stored procedures assist in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.

    Stored procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server. The results do not have to be returned to the client to have the conditional logic applied; all of the work is done on the server...

    BOL is written by Microsoft. This is not an independent point of view.

    They want to convince the reader that s.p. are good.

    The statement "The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure." can be rewritten replacing "stored procedure" with "C++ procedure", or "C# procedure", or "PHP procedure" or whatever language else you use for developing the application.

    And about performances, yes, I recognize that sometime you can get gains using s.p., but the most of the time we are speaking about milliseconds, so this doesn't really matter.