Выдержка из "Application Architecture Guide":

Основное преимущество хранимых процедур в том, что они обеспечивают уровень абстракции для базы данных, а это минимизирует зависимость кода приложения от изменений схемы базы данных. Также упрощается реализация и управление безопасностью, поскольку можно ограничить доступ ко всему, кроме хранимой процедуры, и использовать механизмы безопасности, обеспечивающие детализированную защиту и поддерживаемые большинством баз данных (хотя не забывайте, что это может помешать использовать преимущества пула подключений).

Основное преимущество динамических SQL-выражений в том, что зачастую они считаются более гибкими, чем хранимые процедуры, и могут обеспечить более быструю обработку. Многие инфраструктуры O/RM самостоятельно генерируют динамические запросы, существенно сокращая объем кода, который должен быть написан разработчиками.

Выбирая между хранимыми процедурами и динамическим SQL, руководствуйтесь следующими рекомендациями:

• Для небольшого приложения с единственным клиентом и несколькими бизнес-правилами динамический SQL часто является лучшим выбором.
• Для большого приложения с множеством клиентов продумайте, как обеспечить необходимую абстракцию. Примите решение, где эта абстракция должна находиться: в базе данных в форме хранимых процедур или в слое доступа к данным приложения в форме шаблонов доступа к данным или продуктов O/RM.
• Хранимые процедуры позволяют осуществлять операции с использованием большого количества данных ближе к данным, что может улучшить производительность.
• Использование хранимых процедур для доступа к базе данных позволит максимально сократить зависимость кода приложения от изменений схемы базы данных. Это поможет изолировать и максимально сократить количество изменений в коде приложения при нормализации или оптимизации схемы. Изменения во входных и выходных параметрах хранимой процедуры могут влиять на код приложения, но часто эти изменения можно изолировать в отдельных компонентах, выполняющих доступ к хранимой процедуре. Изолировать и максимально сократить изменения в коде приложения при обновлении схемы помогут и инфраструктуры O/RM.
• Принимая решение об использовании динамических SQL-запросов необходимо понимать, какое влияние будут оказывать изменения в схемах базы данных на приложение. Поэтому следует реализовывать слой доступа к данным таким образом, чтобы он обеспечивал отделение бизнес-компонентов от выполнения запросов базы данных. Такое отделение помогут реализовать такие шаблоны как Query Object и Repository. O/RM-инструменты позволяют полностью отделить бизнес-компоненты от выполнения запросов к базе данных.
• Учтите квалификацию группы, которая будет заниматься разработкой приложения. Если ваши разработчики не знакомы с программированием баз данных, выбирайте инструменты и шаблоны, более привычные вашим разработчиком.
• Подумайте о поддержке отладки. Разработчикам приложения будет проще проводить отладку динамического SQL.

По-моему, в хранимых процедурах должна быть только логика сохранения/загрузки данных из БД. Во многих случаях сформировать пакет данных для создания модели данных приложения удобней, быстрее и эффективнее именно на стороне СУБД. Аналогично и для сохранения данных. А хранение SQL-кода в файлах C# - это удел жеских мужчин.

Есть очень интересная статья Dude, where's my business logic? и ее перевод на русский — Где наша бизнес-логика, сынок?. В статье рассказывается об эволюции архитектуры приложений от однозвенного до n-звенного. В контексте этой темы поднимается вопрос о том, что такое бизнес-логика и в каком слое она должна находиться. Автор рассказывает, что когда появилась двухзвенная архитектура, то перемещение логики работы с данными в хранимые процедуры способствовало повышению производительности приложений. Однако, вместе с кодом для работы с данными часто в хранимки может попасть и бизнес-логика, а это очень плохо.

Базе данных не должно быть дела до того, что такое бизнес-сущность, она должна заботиться только об элементах, используемых для хранения этой бизнес-сущности. У базы данных не должно быть возможности разобраться, какие таблицы должны хранить эту сущность, и она должна работать с таблицами не обращая внимания на бизнес-объект. Задача базы данных – хранить строки в таблицах, которые описывают объект. Кроме базовых ограничений вроде каскадной целостности, типов данных, индексов и пустых значений, база данных не должна иметь функционального знания о том, что же из себя представляет объект в бизнес слое.

Хранимые процедуры, если они есть, должны оперировать только одной таблицей; исключение – это процедуры запрашивающие выборку из нескольких таблиц для выдачи данных. В этом случае, хранимые процедуры работают как представления (view). Представления и хранимые процедуры должны использоваться для консолидации значений, но исключительно для более быстрой и эффективной работы с данными в бизнес слое.

Автор отмечает следующие недостатки присутствия логики в хранимых процедурах:

• Трудности при переходе на другие СУБД.
• Сложность модификации и отладки из-за того, что логика распределена по нескольким слоям.
• Вместе с логикой хранения данных в хранимые процедуры может подмешаться бизнес-логика. Это происходит потому, что проектировщику может показаться «проще» и «логичнее» сделать именно так.

Однако, есть и исключения:

Некоторые пакетные обновления выполняются во много раз быстрее, будучи реализованными с помощью хранимых процедур. В большинстве случаев можно обойтись простым SQL, но некоторые типы пакетных обновлений требуют циклов и при реализации в бизнес слое создадут тысячи SQL команд. В таких редких случаях, должна быть использована хранимая процедура, даже если в ней нужно реализовать бизнес логику. Нужно обратить особое внимание на то, чтобы в ней был реализован только необходимый минимум.

Минимизировать SQL-код БД имеет смысл, если приложение не должно зависеть от сервера БД. Например, когда приложение должно уметь работать с MS SQL Server, Oracle и т.д. В этом случае проще всю логику реализовать в самом приложении.

Когда же приложение разрабатывается под конкретную БД, то часть логики можно вынести в хранимые процедуры. На самом деле, как мне кажется, в этом случае хранимые процедуры образуют интерфейс взаимодействия с базой данных, скрывая от приложения структуру таблиц. Следовательно можно вносить изменения в структуру БД без перекомпиляции приложения.