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"
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.