Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    45

    Unanswered: To write stored procs or not?

    For the last month at work developers have been saying writing stored procs with SQL server 2000 is a waist of time. They site an article on microsoft stating:
    "SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement"

    Reference:
    http://msdn.microsoft.com/library/de...asp?frame=true

    Could this be true?

    -Will

  2. #2
    Join Date
    Nov 2002
    Posts
    24

    Yes but ...

    Yes but it should only compile once and the compiled version is used from then on.

    There are conditions where for example

    * Use of a WITH RECOMPILE clause in the CREATE PROCEDURE or EXECUTE statement.

    * Schema changes to any of the referenced objects, including adding or dropping constraints, defaults, or rules.
    * Running sp_recompile for a table referenced by the procedure.
    * Restoring the database containing the procedure or any of the objects the procedure references (if you are performing cross-database operations).
    * Sufficient server activity causing the plan to be aged out of cache.


    But using stored procedures is the way to go.
    TECMAN

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •