Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Быстрое формирование текста динамического запроса.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Все доброе время суток!

Есть "сложный динамический запрос". В том числе разбирал код чужой процедуры.
Что бы этот код был читабельным по сути написан "один общий динаический запрос в духе

select ...
  from table1 t1
  %SQL_PARAM_1_START%
 inner
  join table2 t2
    on ...
   %SQL_PARAM_2%
   ...
  %SQL_PARAM_1_END%


потом %SQL_PARAM_2% %SQL_PARAM_1_END% %SQL_PARAM_1_START%
функциями replace
заменяются на /*, */, --, ''
Собствено реализовать case/if и мега конкатенацией запрос конечно можн, но сложно и "нечитабильно".
Запрос по тексту "очень большой".

У кого-нить есть идеи "как быстро и читабельно формировать этот запрос" (что бы потом и поддерживать его можно было.

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

Но цель этого топика именно "быстрое формирование СКЛ кода динамического запроса при этом сделать его читабельным, а не как получается если по одним и тем же условями в нескольких местах колхозчть case/if операторы".
26 фев 13, 02:36    [13980381]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
http://technet.microsoft.com/ru-ru/library/ms188332.aspx
EXECUTE ('SELECT ProductID, Name 
    FROM AdventureWorks2012.Production.Product
    WHERE ProductID = ? ', 952)
26 фев 13, 02:59    [13980403]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
наверное не в тему...
26 фев 13, 03:00    [13980404]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
NIIIK
Собствено реализовать case/if и мега конкатенацией запрос конечно можн, но сложно и "нечитабильно".

Если хорошо сделать отступы и расставить ifы, будет вполне читабельно.
26 фев 13, 09:34    [13980702]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
aleks2
Guest
NIIIK
У кого-нить есть идеи "как быстро и читабельно формировать этот запрос" (что бы потом и поддерживать его можно было.


Ваще-то фсе придумали до нас
sp_executesql


Ежели же "параметры" вовсе не параметры в смысле sp_executesql.
То окромя REPLACE у тя нету выбора.
26 фев 13, 09:47    [13980756]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
declare @Query varchar(max) = 'select ...
  from table1 t1
  %SQL_PARAM_1_START%
 inner
  join table2 t2
    on ...
   %SQL_PARAM_2%
   ...
  %SQL_PARAM_1_END%';

declare @ParamValues table (param varchar(100), value varchar(100));
insert into @ParamValues
 (param, value)
values
 ('%SQL_PARAM_1_START%', '/*'),
 ('%SQL_PARAM_1_END%', '*/'),
 ('%SQL_PARAM_2%', '--');
 
select
 @Query = replace(@Query, param, value)
from
 @ParamValues;

select @Query;
26 фев 13, 09:49    [13980765]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Вопрос не о exec ('') или sp_executesql
Я вообще мизирно редко использую exec, а параметры конкатенацией точно не пишу.

Гость333
Если хорошо сделать отступы и расставить ifы, будет вполне читабельно.


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

Реально "не прочитаю" если напишу через case/if конкатенируя текст запроса и будет очень трудоёмко.

Есть "одна идея" (как раз в битовые переменные задонять решения о enable/disable какого-то куска запроса, которые "установлены до шаблона", а потом несколько раз так же enable disable.

проблема в том что "текст запроса очень большой" и сделать все replace занимает 160 мс.

Сам запрос выполняется 350 мс.
26 фев 13, 11:27    [13981361]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
Exproment
Member

Откуда:
Сообщений: 416
NIIIK, если не устраивает время реплейса, то остается только конкатенация строк. Не верю что это будет трудоемко. Скорее всего вы неправильно подходите к процессу. было бы неплохо представить код, о котором идет разговор. Непонятно откуда вы берете свои "теги", типа %SQL_PARAM_1_END% и почему бы вместо них не использовать строковые переменные и конкатенацию.
26 фев 13, 13:27    [13982304]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
NIIIK
Все доброе время суток!

Есть "сложный динамический запрос". В том числе разбирал код чужой процедуры.
Что бы этот код был читабельным по сути написан "один общий динаический запрос в духе

select ...
  from table1 t1
  %SQL_PARAM_1_START%
 inner
  join table2 t2
    on ...
   %SQL_PARAM_2%
   ...
  %SQL_PARAM_1_END%


потом %SQL_PARAM_2% %SQL_PARAM_1_END% %SQL_PARAM_1_START%
функциями replace
заменяются на /*, */, --, ''
Собствено реализовать case/if и мега конкатенацией запрос конечно можн, но сложно и "нечитабильно".
Запрос по тексту "очень большой".
У кого-нить есть идеи "как быстро и читабельно формировать этот запрос" (что бы потом и поддерживать его можно было.




Ты не тем занимаешься.
Выкидывай это говно быстрее, и переписывай на статику, на процедуры.
У тебя их будет может быть в 10 раз больше, но зато они будут поддерживаемые хоть как-то.
В виде динамики это всё неподдерживаемо никак.
26 фев 13, 13:51    [13982562]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Exproment
NIIIK, если не устраивает время реплейса, то остается только конкатенация строк. Не верю что это будет трудоемко. Скорее всего вы неправильно подходите к процессу. было бы неплохо представить код, о котором идет разговор. Непонятно откуда вы берете свои "теги", типа %SQL_PARAM_1_END% и почему бы вместо них не использовать строковые переменные и конкатенацию.


Понимают что конкатенацией возможно, возможно, но "нечитабильно". Там и структура базы вносит "свои прелести".
Названия я "сам придумываю" и ниоткуда не беру. Называются они согласно "читабельно и самоописываемо".
Запрос огроменный, с union-ами и т. п. и часто из-за одного параметар/условия раскирыть/закрыть часть кода надо в нескольких местах (разные джойны, разные наборы столбцов, разные ЦТЕ и т. п.)

>было бы неплохо представить код, о котором идет разговор.
нет, не имею права к сожалению.
Вопрос на 100% соотвествует и в дебри кода всё равно нет смысла лезть.


2 invm
Не спасёт. Они так же выполнятся последовательно.


MasterZiv

Ты не тем занимаешься.
Выкидывай это говно быстрее, и переписывай на статику, на процедуры.
У тебя их будет может быть в 10 раз больше, но зато они будут поддерживаемые хоть как-то.
В виде динамики это всё неподдерживаемо никак.


Я полностью "тем занимаешься".

Лучше сделаю "как можно читабельнее конкатенацией строк". Чем напшиу туеву хучу процедур с разными запросами и "ловить баги" потом во всех сразу.

Опыта у меня в таких делах "жПопой жуй". Да и "умные советы" я пишу не потому что руки чешуться.
26 фев 13, 14:24    [13982835]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
Exproment
Member

Откуда:
Сообщений: 416
MasterZiv, вы видимо все еще считаете что динамика - это несусветное зло. Вообще вы правы :) Но это зло реально позволяет иной раз делать быстрый код.

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

автор
нет, не имею права к сожалению.

Обманываете :) всегда можно сформировать тестовый пример, без контента и без данных, дабы не нарушать пресловутые политики безопасности. А так - вы получили ответ на свой вопрос, но не хотите его даже попробывать, т.к. "нечитабильно". Я могу вас заверить, что в умелых руках будет не только прекрасно читабельно, но еще и понятнее того что есть у вас на данный момент.
26 фев 13, 17:17    [13984306]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Exproment
...

1) это процедура сложного поиска с пейджингом и подсчётом "разных количеств"
2) самый правильный совет наверное "переписать на конкатенацию" чем и занят, читабильным вариант сделаю, пмного копи-паст работы (очень много)
3) динамика в принципе НЕ ЗЛО.
Кривой код - зло.
Динамика позволяет формировать "нужные запросы", с нужным планом.
По сути "любой запрос к СКЛ сервер - это динамика" с SSMS он или с клиента не важно.
Даже если мы вызывает процедуру внутри которой статический код - процедуру с параметрыми передаём динамически.
СКЛ заведомо не знает "какую процедуру вызовим".

Запросы предпочитаю держать в процедурах, а вот "правильной динамики" без СКЛ иньекции и тупой конкатенации переменных в дуе

set @var = '%алек%'
...
where t.name = ' + cast(@var)
...

я не боюсь.
26 фев 13, 17:58    [13984554]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Exproment
автор
нет, не имею права к сожалению.

Обманываете :) всегда можно сформировать тестовый пример, без контента и без данных, дабы не нарушать пресловутые политики безопасности. А так - вы получили ответ на свой вопрос, но не хотите его даже попробывать, т.к. "нечитабильно". Я могу вас заверить, что в умелых руках будет не только прекрасно читабельно, но еще и понятнее того что есть у вас на данный момент.


Упрощённый пример изначально приведён и ответ "заменить на конкатенацию - правильный".
Единственное что пришлось многократно большой текст разбить (в тип nvarchar(max) иначе не преобразовывалос, а кучу cast писать сложнее и нет смысла).
В процессе переписывания в общем. Там не две переменных, поэтому долго.
26 фев 13, 18:03    [13984583]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
NIIIK
пришлось многократно большой текст разбить (в тип nvarchar(max) иначе не преобразовывалос, а кучу cast писать сложнее и нет смысла)

Зачем кучу cast? Можно вообще без них обойтись.
declare @s1 nvarchar(max), @s2 nvarchar(max), @empty_max nvarchar(max) = N'';
set @s1 =              replicate(N'a', 3000) + replicate(N'b', 3000) + replicate(N'с', 3000); -- строка будет обрезана до 4000 символов
set @s2 = @empty_max + replicate(N'a', 3000) + replicate(N'b', 3000) + replicate(N'с', 3000); -- все 9000 символов сохраняются
select len(@s1) as len_s1, len(@s2) as len_s2;

len_s1               len_s2
-------------------- --------------------
4000 9000

(1 row(s) affected)

Это если я правильно уловил суть проблемы.
26 фев 13, 18:31    [13984723]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Гость333

Это если я правильно уловил суть проблемы.


Да, правильно.
Но у меня такой или похожий номер не прокатил почему-то.
тоже попытался первое слагаемое сделать nvarchar(max)
26 фев 13, 18:45    [13984772]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8879
Магия вот тут:

@empty_max nvarchar(max) = N''
27 фев 13, 01:31    [13986025]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
SIMPLicity_
Магия вот тут:

@empty_max nvarchar(max) = N''


Да, проверил на другой задаче.

Так же вспонил за что я люблю именно replace (@sql, '%template%', 'SQL CODE');

метод.

Дело в том обычно делал запросы которые возвращают просто количество и количество это возвращается в output переменной (а не целая статистика по найденным результатам).
Это, конечно, выполняет код "последовательно" (для единичного юзера время суммируется, но в целом сервер равнонагружен).
Так вот в таком подходе я мог использовать "общий шаблон", а потом, начиная с какого-то момента, включать либо одну логику, либо другую (раньше вставлял куски SQL-кода, теперь считаю что весь код надо писать в одном месте и коментами /* */ -- его "вкл/выкл", да текст запроса получается больше, что по факту несущественно, но потом "разбобрать легче".
27 фев 13, 14:11    [13988475]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
dalex1973
Member

Откуда: Польша
Сообщений: 287
NIIIK,

Если хотите сделать код эффективным - то только динамически формируемый SQL.
Но я подозреваю что Вам нужен "универсальный" код, используемый в т.ч. в 1С:

DECLARE @sSQL NVARCHAR (MAX) = 'SELECT M.EntityType,O.* FROM sys.objects O
INNER JOIN (
SELECT  T.object_id,1 AS EntityType FROM sys.triggers T WHERE 1=@ReturnTriggers
UNION ALL
SELECT T.object_id,2 FROM sys.tables T WHERE 1=@ReturnTables
) M On M.object_Id = O.object_id' ;
EXEC sp_executesql @sSQL,N'@ReturnTriggers BIT,@ReturnTables BIT',@ReturnTriggers = 1,@ReturnTables= 1
27 фев 13, 15:50    [13989446]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
dalex1973,

?!

Мне полюбому нужен динамический СКЛ в этой задаче

Вопрос стоял "как его формировать быстро и эффективно".

В изначачально задаче у меня "вызов процедуры для отображения страницы с данными" и "вызов процедура для подсчёта количеств (в том числе количества строк)" отдельные. Там можно формировать обычной конкатенацией и код довльно читабельный. по факту используется "общий шаблон" а динамически подставяются --, /*, */ закрывая/открывая нужные участки кода.

В случае, когда количество строк возвращается одновременно со страницей - часть логики формирования Д-СКЛ надо "раздвоить" после определённого момента и если "не разбить шаблон на куски" - этого сделать НЕ получится.

Вот, значительно более простой пример который я могу выложить (тут формируется любым сопосбом быстро).
Сегодня "дописывал". Я обычно "не куски кода заменяю", а он заведомо написан в основном шаблоне, а потом, как сказал раньше, я его "прячу или открываю".

/*
   exec [report].[spLeaveRequestGetV2] @fkDocument = 'ab3d88a3-4890-e111-91b3-00155d894a57';
   exec [report].[spLeaveRequestGetV2] @fkOwner = '6663802f-ef56-e111-b541-00155d894a57';
   exec [report].[spLeaveRequestGetV2] @fkCheckDate = '2012-04-29 00:00:00.000';
   exec [report].[spLeaveRequestGetV2] @fkCheckDatePeriodStart = '2012-08-28'

   declare @totalRowCount int = -1;
   exec [report].[spLeaveRequestGet]
    @pageSize = 10,
	@pageIndex = 2,
	@totalRowCount = @totalRowCount output;
   select @totalRowCount;

*/
alter PROCEDURE [report].[spLeaveRequestGetV2]
(
	@pk uniqueidentifier = null,          -- Primary key filter.
	@lrDateStart datetime = null,         -- Date filter (start of range).
	@lrDateEnd datetime = null,           -- Date filter (end of range).
	@fkOwner uniqueidentifier = null,     -- Filter by owner.
	@fkDocument uniqueidentifier = null,  -- Filter by document.
	@fkLeaveType uniqueidentifier = null, -- Filter by leave type.
	@fkCheckDate datetime = null,
	@xmlOwner xml = null,
	@approved bit = null,
	@fkCheckDatePeriodStart datetime = null,
	@fkCheckDatePeriodEnd datetime = null,
	
	--http://redmine.ruswizards.com:8100/redmine/issues/6406
	@pageSize int = null,
	@pageIndex int = null,
	--pass any "not null" value for coputing (-1 for example)
	@totalRowCount int = null output

)
AS
BEGIN
 
 declare 
  @lastRow int = @pageIndex * @pageSize,
  @firstRow int = (@pageIndex - 1) * @pageSize + 1;

 declare 
  @SQL_PAGING_START nvarchar(2) = '/*',
  @SQL_PAGING_END nvarchar(2) = '*/',
  @SQL_NO_PAGING_START nvarchar(2) = '',
  @SQL_NO_PAGING_END nvarchar(2) = '';

 if @lastRow > 0
 begin
  select @SQL_PAGING_START = '', 
         @SQL_PAGING_END = '',
         @SQL_NO_PAGING_START = '/*',
         @SQL_NO_PAGING_END = '*/';		 
 end;
  	
 declare @sql nvarchar(max);
 set @sql = cast(N'' as nvarchar(max)) + --SQL magic (workarund for nvarchar(MAX))
 '
 
 	declare @ENTITY_HT_LEAVE_REQUEST uniqueidentifier = dbo.fnCommonDictionaryGetPkBySyn(''ENTITY_HT_LEAVE_REQUEST'');
	declare @DIC_ATTR_LEAVE_REASON uniqueidentifier = dbo.fnCommonDictionaryGetPkBySyn(''DIC_ATTR_LEAVE_REASON'');
	declare @DIC_ATTR_LEAVE_TYPE uniqueidentifier = dbo.fnCommonDictionaryGetPkBySyn(''DIC_ATTR_LEAVE_TYPE'');
	declare @DIC_ATTR_APPLICATION_DATE uniqueidentifier = dbo.fnCommonDictionaryGetPkBySyn(''DIC_ATTR_APPLICATION_DATE'');
	declare @DIC_ATTR_FROM_DATE uniqueidentifier = dbo.fnCommonDictionaryGetPkBySyn(''DIC_ATTR_FROM_DATE'');
	declare @DIC_ATTR_TO_DATE uniqueidentifier = dbo.fnCommonDictionaryGetPkBySyn(''DIC_ATTR_TO_DATE'');
	declare @DIC_ATTR_CONTACT_NO uniqueidentifier = dbo.fnCommonDictionaryGetPkBySyn(''DIC_ATTR_CONTACT_NO'');
	declare @DIC_ATTR_DOCUMENT uniqueidentifier = dbo.fnCommonDictionaryGetPkBySyn(''DIC_ATTR_DOCUMENT'');
	declare @DIC_LEAVE_REQUEST_STATUS uniqueidentifier = dbo.fnCommonDictionaryGetPkBySyn(''DIC_LEAVE_REQUEST_STATUS'');

with cteResult as
( 
select %SQL_PAGE_QUERY_START%
       ' + @SQL_PAGING_START + '
       top (@lastRow)       
	   ' + @SQL_PAGING_END + '

	   lr.pk as pk,
       lt_cd.pk as fkLeaveType,
       lt_cd.name as leaveTypeName,
       lr_dc.clobValue as leaveReason,
       ad_edd.dateValue as applicationDate,
       fd_edd.dateValue as fromDate,
       td_edd.dateValue as toDate,
       cn_eds.stringValue as contactNo,
       td.WBUID as WBUID,
       td.pk as documentId,
       td.fkCreator as creatorId,
       td.approved as approved,
       cmd_synStatus.synonym as someStatus,
	   '
	   + @SQL_NO_PAGING_START +
	   'cast(null as int) as rowNumber'
	   + @SQL_NO_PAGING_END +
	   + @SQL_PAGING_START + 	   
	   '
	   row_number() over (order by ad_edd.dateValue) as rowNumber
	   ' + @SQL_PAGING_END + '
	   %SQL_PAGE_QUERY_END%

	   %SQL_COUNT_QUERY_START%
	   count(1) as totalRowCount
	   %SQL_COUNT_QUERY_END%

  from dbo.tEntity as lr with(nolock) -- Leave Request base.
  
 inner
  join dbo.tEntityDataKeys as lt with(nolock)  -- Leave type link.

 inner
  join dbo.tCommonDictionary as lt_cd with(nolock) -- Leave type.
    on lt.fkValue = lt_cd.pk
   and lt_cd.deleted = 0
   
    on lt.fkEntity = lr.pk
   and lt.fkAttribute = @DIC_ATTR_LEAVE_TYPE
   %LEAVETYPE%
   and lt.deleted = 0
  
  left
  join dbo.tEntityDataClob as lr_dc with(nolock)  -- Leave reason.
    on lr_dc.fkEntity = lr.pk
   and lr_dc.fkAttribute = @DIC_ATTR_LEAVE_REASON
   and lr_dc.deleted = 0
   
 inner
  join dbo.tEntityDataDate as ad_edd with(nolock)  -- Application date.
    on ad_edd.fkEntity = lr.pk
   and ad_edd.fkAttribute = @DIC_ATTR_APPLICATION_DATE
   and ad_edd.deleted = 0
   
 inner
  join dbo.tEntityDataDate as fd_edd with(nolock)  -- From date.
    on fd_edd.fkEntity = lr.pk
   and fd_edd.fkAttribute = @DIC_ATTR_FROM_DATE
   %FROM_DATE_FILTER%
   %CHECK_DATE_FROM%
   and fd_edd.deleted = 0
   
   
 inner
  join dbo.tEntityDataDate as td_edd with(nolock)  -- To date.
    on td_edd.fkEntity = lr.pk
   and td_edd.fkAttribute = @DIC_ATTR_TO_DATE
   %TO_DATE_FILTER%
   %CHECK_DATE_TO%
   and td_edd.deleted = 0
  
   
 inner
  join dbo.tEntityDataShortString as cn_eds with(nolock)  -- Contact No.
    on cn_eds.fkEntity = lr.pk
   and cn_eds.fkAttribute = @DIC_ATTR_CONTACT_NO
   and cn_eds.deleted = 0
   
 inner
  join dbo.tEntityDataEntity as d_ede with(nolock)  -- Document link.

 inner
  join approve.tDocument as td with(nolock) -- Document.
    on td.pk = d_ede.fkValueEntity
   and td.deleted = 0
   %OWNER_FILTER%
   %APPROVED_FILTER%
   
 %XML_OWNER_FILTER%
   
    on d_ede.fkEntity = lr.pk
   and d_ede.fkAttribute = @DIC_ATTR_DOCUMENT
   and d_ede.deleted = 0
   %DOCUMENT_FILTER%
   
  left
  join dbo.tEntityDataKeys as edk_status with(nolock) -- status for LR
    on edk_status.fkEntity = lr.pk
   and edk_status.fkAttribute = @DIC_LEAVE_REQUEST_STATUS
   and edk_status.deleted = 0

  left
  join dbo.tCommonDictionary AS cmd_synStatus with(nolock)
    on cmd_synStatus.pk = edk_status.fkValue
      
 where lr.fkType = @ENTITY_HT_LEAVE_REQUEST
   and lr.deleted = 0    
   
   %WHERE%
%SQL_PAGE_QUERY_START%
' + @SQL_PAGING_START + '
 order 
    by ad_edd.dateValue
' + @SQL_PAGING_END + '
%SQL_PAGE_QUERY_END%
)

%SQL_PAGE_QUERY_START%
select r.* --bad approach but it''s possible in paging logic
  from cteResult r
' + @SQL_PAGING_START + '
 where r.rowNumber >= @firstRow
' + @SQL_PAGING_END + '
%SQL_PAGE_QUERY_END%

%SQL_COUNT_QUERY_START%
select @totalRowCount = r.totalRowCount
  from cteResult r
%SQL_COUNT_QUERY_END%
   ';
    
      
set @sql
	=
	replace
	(@sql,
	'%FROM_DATE_FILTER%',
	case
		when @lrDateEnd is not null
			then ' and fd_edd.dateValue <= @lrDateEnd '
		else ''
	end);
	
set @sql
	=
	replace
	(@sql,
	'%TO_DATE_FILTER%',
	case
		when @lrDateStart is not null
			then ' and td_edd.dateValue >= @lrDateStart '
		else ''
	end);
      
set @sql 
    = 
    replace
    (@sql, '%OWNER_FILTER%', 
    case
  			when @fkOwner is not null
  			then 'and td.fkCreator = @fkOwner'
  			else ''
    end);
      
set @sql 
    = 
    replace
    (@sql, 
     '%DOCUMENT_FILTER%', 
     case
  			 when @fkDocument is not null
  			 then 'and d_ede.fkValueEntity = @fkDocument'
  			 else ''
     end);
     
set @sql 
    = 
    replace
    (@sql, 
     '%LEAVETYPE%', 
     case
  			 when @fkLeaveType is not null
  			 then 'and lt.fkValue = @fkLeaveType'
  			 else ''
     end);
      
set @sql 
    = 
    replace
    (@sql, 
     '%WHERE%', 
     case
    		when @pk is not null
    		then 'and lr.pk = @pk'
    		else ''
     end +
     case
    		when @fkCheckDate is not null
    		then 'and @fkCheckDate between fd_edd.dateValue and td_edd.dateValue'
    		else ''
     end
     );

set @sql = REPLACE(@sql, '%XML_OWNER_FILTER%',
  case
    when @xmlOwner is not null
    then '
  inner
   join dbo.fnXmlGetPrimaryKey(@xmlOwner) xOwner
     on xOwner.pk = td.fkCreator
    '
    else ''
  end);
  
set @sql = replace(@sql, '%APPROVED_FILTER%',
  case
    when @approved is not null
    then 'and td.approved = @approved'
    else ''
  end);
        
set @sql = replace(@sql, '%CHECK_DATE_FROM%',
  case
    when @fkCheckDatePeriodEnd is not null
    then 'and @fkCheckDatePeriodEnd >= fd_edd.dateValue'
    else ''
  end);
  
set @sql = replace(@sql, '%CHECK_DATE_TO%',
  case
    when @fkCheckDatePeriodStart is not null
    then 'and @fkCheckDatePeriodStart <= td_edd.dateValue'
    else ''
  end);

--select cast(@sql as xml) queryText

declare @sqlCount nvarchar(max);
if @totalRowCount is not null
begin
 set @sqlCount = @sql;
end;

set @sql = replace (@sql, '%SQL_PAGE_QUERY_START%', '');
set @sql = replace (@sql, '%SQL_PAGE_QUERY_END%', '');
set @sql = replace (@sql, '%SQL_COUNT_QUERY_START%', '/*');
set @sql = replace (@sql, '%SQL_COUNT_QUERY_END%', '*/');

exec sp_executesql
 @sql,
 N'
 @pk uniqueidentifier,     
 @lrDateStart datetime,      
 @lrDateEnd datetime,         
 @fkOwner uniqueidentifier,
 @fkDocument uniqueidentifier,
 @fkLeaveType uniqueidentifier,
 @fkCheckDate datetime,
 @xmlOwner xml,
 @approved bit,
 @fkCheckDatePeriodStart datetime,
 @fkCheckDatePeriodEnd datetime,
 @firstRow int,
 @lastRow int',
        
 @pk = @pk,     
 @lrDateStart = @lrDateStart,      
 @lrDateEnd = @lrDateEnd,         
 @fkOwner = @fkOwner,
 @fkDocument = @fkDocument,
 @fkLeaveType =  @fkLeaveType,
 @fkCheckDate = @fkCheckDate,
 @xmlOwner = @xmlOwner,
 @approved = @approved,
 @fkCheckDatePeriodStart = @fkCheckDatePeriodStart,
 @fkCheckDatePeriodEnd = @fkCheckDatePeriodEnd,
 @firstRow = @firstRow,
 @lastRow = @lastRow;

if @totalRowCount is null
begin
 return;
end;

set @sql = @sqlCount;
set @sql = replace (@sql, '%SQL_PAGE_QUERY_START%', '/*');
set @sql = replace (@sql, '%SQL_PAGE_QUERY_END%', '*/');
set @sql = replace (@sql, '%SQL_COUNT_QUERY_START%', '');
set @sql = replace (@sql, '%SQL_COUNT_QUERY_END%', '');

--select cast(@sql as xml) queryText
 
exec sp_executesql
 @sql,
 N'
 @pk uniqueidentifier,     
 @lrDateStart datetime,      
 @lrDateEnd datetime,         
 @fkOwner uniqueidentifier,
 @fkDocument uniqueidentifier,
 @fkLeaveType uniqueidentifier,
 @fkCheckDate datetime,
 @xmlOwner xml,
 @approved bit,
 @fkCheckDatePeriodStart datetime,
 @fkCheckDatePeriodEnd datetime,
 @totalRowCount int output',
        
 @pk = @pk,     
 @lrDateStart = @lrDateStart,      
 @lrDateEnd = @lrDateEnd,         
 @fkOwner = @fkOwner,
 @fkDocument = @fkDocument,
 @fkLeaveType =  @fkLeaveType,
 @fkCheckDate = @fkCheckDate,
 @xmlOwner = @xmlOwner,
 @approved = @approved,
 @fkCheckDatePeriodStart = @fkCheckDatePeriodStart,
 @fkCheckDatePeriodEnd = @fkCheckDatePeriodEnd,
 @totalRowCount = @totalRowCount output;

END


Код не мой, но пейджинг там "свежедобавленный" мной.
27 фев 13, 16:19    [13989699]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
NIIIK
...цель этого топика именно "быстрое формирование СКЛ кода динамического запроса ...

Так формируйте его быстро. Не в SQL.
27 фев 13, 16:25    [13989730]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
ambarka_max
NIIIK
...цель этого топика именно "быстрое формирование СКЛ кода динамического запроса ...

Так формируйте его быстро. Не в SQL.


Я скорее напишу отдельную процедуру/функцию
из-за этих

set @sql = replace (@sql, '%SQL_PAGE_QUERY_START%', '/*');
set @sql = replace (@sql, '%SQL_PAGE_QUERY_END%', '*/');
set @sql = replace (@sql, '%SQL_COUNT_QUERY_START%', '');
set @sql = replace (@sql, '%SQL_COUNT_QUERY_END%', '');


которая формирует просто код дважды (2 * близкое к нулю всё равно ноль) но без реплейсов чем эту логику на приложение перенесу (особенно подразумевая что приложение может потом быть "любое на любой платформе").
27 фев 13, 16:30    [13989761]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
PaulYoung
Member

Откуда: Москва
Сообщений: 2567
MasterZiv
В виде динамики это всё неподдерживаемо никак.
+1, тошнит от такого... а разработчика так хочется взять и у@@@@ь
27 фев 13, 16:36    [13989803]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
PaulYoung
MasterZiv
В виде динамики это всё неподдерживаемо никак.
+1, тошнит от такого... а разработчика так хочется взять и у@@@@ь


Лучше того кто создаст кучу процедур для одной сущности из-за каждого нового параметра.
Особенно если их "валом".

А пока и "более сложные поддерживаются", когда написано читабельно.
А "сформировать из динамического статический написав в нехитрую строчку" не такая проблема, потом "дорисовать нужный кусок по нужному параметру", отладить и вставить в процедуру.
27 фев 13, 16:55    [13989952]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
Exproment
Member

Откуда:
Сообщений: 416
PaulYoung
+1, тошнит от такого... а разработчика так хочется взять и у@@@@ь

Аргументация 90 lvl.
28 фев 13, 11:03    [13992660]     Ответить | Цитировать Сообщить модератору
 Re: Быстрое формирование текста динамического запроса.  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Не тратьте чужое время своими малолетне-пацанскими коментами,
читайте внимательно вопрос,
не разводите холивары.

Оставить комен лишь бы попиндеть - либо тема с ник-нейм другого пользователя, либо своя собственная (и желательно не в этой ветке).
28 фев 13, 11:18    [13992768]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить