>To me the biggest drawback to using SPs is it makes it very difficult to give the user a UI where they can do whatever query they want. Power to the user is what PCs were originally designed for.
>
>It doesn't always have to be that way. Maybe you can give some specifics from a real-world example, where you feel the UI limits the user?
>
>I've seen some pretty elaborate UIs that allow the user to customize and tailor a result set, where SPs were running in the background.

So have I. Here's a simple one:

lblName txtName cmdSearch
lblPhone txtPhone cmdClear

Pass two parameters to the SP. Piece of cake.

Oops. The user cannot search on address. IMO this design is easy to build and the user doesn't know any better so it persists.

To add address is simple, right? Modify the screen, modify the SP, blah, blah, blah. Lame.

How dare we dictate to the user what they can and cannot do by implementing this design?

How about take this to a logical extent? Give the user the ability to create a somewhat dynamic query using the fields in the table(s) that create the result set for the current screen.

An SP to support that UI is difficult.