2Moth

Я лично вначале думал так же, как и Вы. НО ... прошло время, накопился опыт и могу заявить: динамичексий запрос сам по себе вещь хорошая и нужная, но
- выполняется он в контексте пользователя, а не владельца процедуры, формирующей этот запрос. Поэтому придется раздавать права пользователям(напрямую или через роли не суть важно) на таблицы/представления, хотя бы на SELECT, а значит у пользователя появляется возможность выбирать из них данные(хоть через тот же Excel) не по-вашему сценарию. Я думаю, что в любой фирме может найтись "умелец", который способен на это. Вы скажите - ну так раздадим всем нужные права, я отвечу - ну да и будем тратить время на мэнеджирование каждой таблицы/представления (конечно, есть еще application role, но их использование не всегда возможно, например при доступе через http, asp)

- если вдруг понадобиться разделить права доступа к отчетам/поискам для разных пользователей, то придется создать две(три, четыре, ...) процедуры, которые будут выполнять в принципе одно и тоже. Но универсальность (к которой вы, я так понял, стремитесь) будет потеряна. (Можно опять же проверять пользователя внутри процедуры, но придется создать дополнительные таблицы, где прописаны права того или иного пользователя)
В итоге все равно вы будете иметь несколько(может и много) процедур плюс дополнительную работу по мэнеджированию прав пользователей на другие объекты.

Универсализация вещь хорошая, но не панацея

ЗЫ
Из личного опыта - у меня на данный момент около 150(и будет больше) различных отчетов и поисков и все они офромлены как отдельные процедуры. Главное - выделить действительно часто повторяющиеся части запросов и постараться как-то унифицировать их, помня об особенностях MS SQL. Например, любые даты в MS SQL находятся в диапазоне 01.01.1753 - 31.12.9999 и эти как раз эти значения могут принимать по умолчанию входные параметры, задающие в вашей процедуре период. В SQL2000 появилиь функции, которые так же облегчают написание общих частей запроса, но пользоваться ими нужно осторожно(можно сильно потерять в производительности)

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

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

1. Даты.
Про ограничения содержимого типа datetime я уже говорил выше, но кроме этого существуют чисто логические ограничения. Например, вряд ли у вас есть счета за 1896 год и за 1955 год наверное нет и даже за 1990 год вряд ли(хотя кто знает ). Соответственно не должно быть пока счетов за 2005 год(не будем пока говорить об ошибках ввода).

Т.е. можно в процедуре задать для входных параметров, определяющих период, задать ограничения
CREATE PROCEDURE myproc
@period_begin datetime = '19900101 00:00:00',
@period_end datetime = '99991231 23:59:59' - это на всякий случай
AS .....

и вот в любом случае имеем нединамический запрос, который будет работать при любых входных параметрах
SELECT * FROM mytable WHERE mydate BETWEEN @period_begin AND @period_end

Опять же для удобства можно определить действительно глобальные значения в системе и хранить их как предложено, например, здесь http://www.swynk.com/friends/green/uservar.asp

2. Клиенты
2.1 Если клиент идентифицируется каким-либо символьным полем - именем, кодом, ИНН(кажется есть что-то такое?) - то можно осуществлять выборку использую не прямое сравнение а LIKE
А именно

CREATE PROCEDURE myproc
@period_begin datetime = '19900101 00:00:00',
@period_end datetime = '99991231 23:59:59',
@client_code varchar(15) = ''
AS

SET @client_code = @client_code + '%'

SELECT * FROM clients WHERE client_code LIKE @client_code

SELECT * FROM mytable a
INNER JOIN clients b ON b.client_code = a.client_code AND b.client_code LIKE @client_code
WHERE a.mydate BETWEEN @period_begin AND @period_end

Т.е. с помощью входных параметров и одного запроса можно получить 4 различных результата.

2.1 Если клиент идентифицируется каким-либо полем типа int, то придется усложнить код

CREATE PROCEDURE myproc
@period_begin datetime = '19900101 00:00:00',
@period_end datetime = '99991231 23:59:59',
@client_code int = -1
AS

SELECT * FROM clients WHERE client_code = CASE @client_code WHEN -1 THEN client_code ELSE @client_code END

SELECT * FROM mytable a
INNER JOIN clients b ON b.client_code = a.client_code AND b.client_code = CASE @client_code WHEN -1 THEN b.client_code ELSE @client_code END
WHERE a.mydate BETWEEN @period_begin AND @period_end

ну и так далее. Немножко знаний об определения условий в теле запроса можно почерпнуть вот здесь http://www.osp.ru/win2000/sql/2001/06/075.htm (правда там только насчет ORDER, но все-таки)


Теперь об уровнях процедур и полях вывода.
Деление на уровни - это логическое деление, т.е. нужно просто определиться какие процедуры не будут напрямую вызываться конечным пользователем, а только другими процедурами. Вот у вас и получиться "нижний" уровень процедур.

Поля вывода - т.к. в запросах равно могут участвовать и таблицы и представления, то если у нас имеется представление myview вроде

SELECT a.conto_nr, a.mydate, a.conto_summa, b.client_name, b.cleint_code FROM mytable a
INNER JOIN clients b ON b.client_code = a.client_code

то процедура из моего примера может быть преобразована так

CREATE PROCEDURE myproc
@period_begin datetime = '19900101 00:00:00',
@period_end datetime = '99991231 23:59:59',
@client_code int = -1
AS

SELECT * FROM myview a
WHERE a.mydate BETWEEN @period_begin AND @period_end
AND a.client_code = CASE @client_code WHEN -1 THEN a.client_code ELSE @client_code END

т.е. меняя количество выбираемых полей в представлении, можно без изменения текста процедуры(!процедур!) изменить количество информации, получаемое конечным пользователем.

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

Резюме.
У динамического запроса, если брать его как один запрос, есть одно неоспоримое преимущество - он будет выполняться быстрее(или не медленне) универсального явного запроса по той причине, что содержит только те условия поверки, которым действительно задал пользователь.

Понятно что запрос
exec('SELECT * FROM clients')
сработает быстрее, чем
SET @client_code = '%'
SELECT * FROM clients WHERE client_code LIKE @client_code

НО IMHO в любой _системе_ нужно уделять внимание и дальнейшей поддержкеи развитию. Открытость, масштабирумость, простота(читай - дешивизна) администрирования хоть и высокие слова(может быть и набившие уже оскомину), но все-таки к ним надо хотя бы стремиться. Поэтому повторюсь - только использование динамических запросов, а равно как предложенных здесь идей есть однобокое, а следовательно неправильное, решение. Комбинировать нужно, комбинировать

PS
Надеюсь, что не очень утомил Вас