So the query is a little more complicated then what was in the previous post?
I'm surprised that you're seeing that much of a speed increase. It's still dynamic sql. SQL Server still has to perform the same operations that it would have to do if an ad-hoc query was submitted.
Are you wrapping the query in sp_executesql()? The purpose of this system stored procedure is to help sql server parameterize the query and cache it.
-Mike
>I agree that i would be easier to build the the app but it was just to slow. By using a procedure I cut the execution time from 4-5 minutes to apx 20 seconds. The stored procedure I created runs fine but it needs some flexibility. One thing it needs to be able to do is to change from an inner to a left outer join if a paramteter is passed or add a piece to the where clause if necessary. What I want to avoid is haveing multiple procedures called from one button (that only gets a cursor for a report).