I share your pain.... I am really shocked that MS hasn't addressed this need yet. I have beat my head up against this several times in the last few months.

Maybe your SET ROWCOUNT suggestion is the way to go. From my quick test, SET ROWCOUNT seems to be scoped to the procedure in which it is specified.




>>Hey Erik. I've worked around this by constructing the statement as a string, and using EXECUTE to run the query. Downside (and a biggie) is that SQL Server won't be able to pre-compile and all that good stuff.
>
>As I replied to Bob, this downside is a showstopper in my case. This SProc is going to be _very_ heavily used, and the need to compile on every hit would be a majorly costly endeavor ($ wise).