Unless you manage to get an execution plan that can be reused (and usually you don't) then you can say there is no performance benifit.

For me, the benefit is that no changes have to be made to the web site or internal applications for most changes in the stored procedure. Particularly useful when I find a quicker way to get the same result set.

You could realize a slight performance benifit because you would not have to send thousands of characters from the client to the server. With the dynamic SP you simply send the call and the code is built server side and then immediately executed.

Robert W. Marda

SQL Programmer

bigdough.com

The world’s leading capital markets contact database and software platform.