stored procedures, compile or not

I am writing stored procedures to retrieve data from an OLTP application that will be adding several hundred thousand transactions a day. The user will be presented a screen to select up to a dozen or so search criteria; date range, color, size, processing plant, etc. They may select all criteria, one or two, or none.

Currently I am creating a stored procedure "on the fly" by replacing temp vars with search criteria, compiling and executing.

My option would be to compile the stored procedure, pass it parameters, and run the precompiled stored procedure.

I am wondering, as the database is changing so much, if I am better off to compile the procedure every time it is run. Or if stored procedures are always the best way to go.

I plan to incorporate an update statistics in my maintenance plan with each night's back ups.

Stored procedures are only really beneficial if they're used many times. One-off stored procedures offer no performance benefits and can severely clutter the database.

As for changing data, SQL Server will automatically recompile stored procedures if the tables they use haved changed significantly.

-Ryan / Kardax

>>Only if you are going to reuse the procedure...

But even if you are not going to regularly reuse the stored procedure (perhaps execute it once a day for instance) then it is often easier to maintain, test and deploy stored procedures than it is to maintain, test and deploy the code routines that generate adhoc SQL.

The WITH RECOMPILE option can also be useful if your stored procedure's input parameters vary wildly between executions where the currently-cached plan (from a previous execution) is not necessarily the most optimal plan for the current set of parameters.

Chris

Thanks everyone. After further research I discovered I was opening the age old dynamic sql vs stored procs battle. I think I have the information necessary to proceed. My problem is passing 8 or 10 parameters and the logic necessary if a parameter is null, but that is an issue I can deal with. I just didn't want to go to all the trouble of rewriting a hundred dynamic sql scripts into stored procs if there was no benefit to be derived.

Thanks again.