Force SQL Server to recompile stored procedures every time they run (SQL Server 7/2000)

This is a solution for a very specific problem, and it's one that you'll hardly ever use, but it's important to know about that one scenario where it can save your neck. Ordinarily, stored procedures are only recompiled if they're no longer in the procedure cache. But if a stored procedure's execution plan is still in the cache, then SQL Server reuses the compiled stored
procedure and its existing execution plan. This is almost always the best course of action. Almost always, but not always.
Sometimes, however, reusing an existing plan doesn't offer the most efficient performance. Imagine, for example, that your stored procedure accepts a parameter that determines the nature
of a JOIN operation. The results can vary in a big way, so you wouldn't want your procedure to be locked into an execution plan that might be completely inappropriate for that JOIN. In a highly
specialized case like this, you might want to force SQL Server to recompile the procedure every time the procedure runs. Doing so comes at a performance cost, but this might be offset by the
savings you gain in not executing the procedure with an awful compiled execution plan. Consider carefully whether to use this approach (or whether to re-engineer the over-design of your
application to avoid this situation in the first place). Should you need to instruct SQL Server to recompile each time, add the WITH RECOMPILE directive to the procedure, like this:
CREATE PROCEDURE ProcName
@.Param int /* ... other parameters */
WITH RECOMPILE
AS /* ... procedure code follows */

If we omit "WITH RECOMPILE", what will be the consequence? Thanks

WITH RECOMPILE can kill an Asp.net application because HTTP is stateless. The better solution is to force SQL Server to put all your stored procs in the procedure cache on start up. There is a stored proc in the Master called SP(system stored proc) Procoption you can use it to auto start all your stored procs. Recompile is modified in SQL Server 2005 you can recompile only the line you need then your solution will be ok for now there are alternatives. See code below the only value for option is Startup and value is true for ON and false for OFF. Hope this helps.

sp_procoption[@.ProcName =]'procedure'
,[@.OptionName =]'option'
,[@.OptionValue =]'value'

|||

Please kindly elaborate more on: "WITH RECOMPILE can kill an Asp.net application because HTTP is stateless." What do "kill" and "stateless" mean here? Thanks again.

|||

You get the best performance if all your stored procs are in the procedure cache all the time WITH RECOMPILE will not allow that, so everytime your stored proc is accessed your user will wait for SQL Server to recompile the stored proc before executing it. In Asp.net some processes will time out before your stored proc will execute. Kill means your code will timeout and stateless means a protocol without state HTTP is one of them. Your users will wait for SQL Server which is session to finish before objects on your pages can be accessed. The first thing to know about stored procs is avoid Recompile even in Windows appilcation. Hope this helps.