IMO, data access/manipulation logic (Selects, Inserts, Updates, and Deletes) belong in SP's. I support this argument on 3 levels: (security, performance, and flexibility)

Security

Even if you think security can be adequately managed in other ways, SP's offer an easier, less cumbersome alternative. If you manage security on the tables, then you may need to mange invidual select, insert, update, and delete permissions for every table. And perhaps, you may need to manage it at the column level as well. This may necessitate the need for other db objects such as views. OTHO, if you use SP's, you need only manage execute rights. Within the proc, you can manage what gets returned based on the user and/or role. This ties into the flexibility argument below.

Performance

Regardless of opnions to the contrary, SP's have a performance benefit over their rendered SQL counterparts. Because they are persisted db objects, they get the benefit of a stored execution plan. There is no need to run 1x to get the code compiled and the plan cached. Be careful about benchmarks you read. Unless you can EXACTLY match the environment, the numbers are meaningless. Further, most of the published "benchmarks" you see here or in other "communities", do not publish their protocols or methodologies, assuming any are used. For more info on benchmarking, check out the benchmarking handbook, edited by Jim Gray.


Flexibility

SP's give you the flexibility of mixing procedural and SQL code. Most importantly, SP's allow the code to be hosted in the DBMS itself. This means your logic is portable to any client that can connect to the server. This means that if the only thing you have access to is ASP and a script langage only, your data access/update logic is available. OTHO, if you embed this logic in middle tier components, you may not be assured of having the logic available, either becuase of technical or political/mgt reasons. Having said this, I do advocate that whenever possible, you build components that broker access to the SP's. i.e., don't use OLE-DB/ODBC directly.

This has been a "spirited" debate in the past. What I have outlined above, while a massivily cut-down version of things I have posted in the past, it is the core parts of my "Use SP's" argument. In the past 36 months, I have delivered either myself or part of a team 9+ mission critical apps that employ the use of SQL Server SP's exclusively.

Whatever decision you make, think it through carefully.

Good Luck..