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

    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"


    Could this be true?


  2. #2
    Join Date
    Nov 2002

    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.

Posting Permissions

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