MSSQL : Расчет нарастающим итогом, без курсора

Обожаю классические задачи, вот и сегодня наткнулся на одну из них.

Суть была такова:

Сейчас переписываем систему оповещений о результатах выполнения процедур.

Результат кладется в таблицы и с использованием sp_send_dbmail рассылается адресатам.

Но есть одна проблема, рассылаемый объем не может превышать 1000000 байт (ограничение MS SQL).

Ладно ок, SQL не хочет, тогда надо вводить ограничение, а как?

Имперический метод себя не оправдает, так как объем вычисляется по суммарной длине строк подзапроса (про запрос см. MSDN) и ограничение по ширине строки тоже, тогда нам необходимо заранее рассчитать: а сколько байт будет наше сообщение.

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

Так давайте решим задачу "нарастающего итога"

Пусть у нас есть таблица только с одной ячейкой самого сообщения, например:

1. CREATE TABLE msgbodytosend
2.  (
3.      mgsstring VARCHAR(max)
4.  );

* This source code was highlighted with Source Code Highlighter.

Первый вариант - использование одного запроса.

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

Дальше используя произведение (INNER JOIN) таблиц и суммирование длин, получаем необходимый результат.

Подзапросы я предпочитаю оборачивать в CTE выражения. На выходе получаем следующий код:

1. WITH
2. cte_msgbody AS (
3.     SELECT row_number() (ORDER BY msgstring) row
4.         , msgstring
5.         , len(msgstring) msgstring_len
6.     )
7. SELECT
8.     mb_l.row
9. ,  mb_l.msgstring
10. ,  sum(mb_r.msgstring_len) Running_Total
11. FROM cte_msgbody mb_l
12. INNER JOIN cte_msgbody mb_r ON mb_l >= mb_r
13. GROUP BY
14. mb_l.row
15.     , mb_l.msgstring
16. HAVING sum(mb_r.msgstring_len) < 1000000

* This source code was highlighted with Source Code Highlighter.

Второй вариант разбить решение на составные части.

В данном случае мы убираем CTE выражение и делаем предварительный расчет во временной таблице.

1. -- Удалим временную таблицу если вдруг она использовалась ранее в тексте
2. IF object_id('tempdb..#msgbody')
3.     DROP TABLE #msgbody;
4.  
5. -- И создадим ее за ново.
6. CREATE TABLE #msgbody (
7.     row INT identity(1, 1) PRIMARY KEY NOT NULL
8.     , msgstring VARCHAR(max) NULL
9.     , msgstring_len INT NULL
10.     , running_total INT NULL
11.     );
12.  
13. -- Заполняем таблицу
14. INSERT INTO #msgbody (
15.     msgstring
16.     , msgstring_len
17.     )
18. SELECT msgstring
19.     , len(msgstring)
20. FROM msgbodytosend
21.  
22. -- Теперь сделаем расчет
23. UPDATE mb
24. SET running_total = (
25.         SELECT sum(msgstring_len)
26.         FROM #msgbody mb_i
27.         WHERE mb_i.row <= mb.row
28.         )
29. FROM #msgbody mb
30. -- Добавим небольшое ограничение, этот кусок можно улучшить
31. WHERE (
32.         SELECT sum(msgstring_len)
33.         FROM #msgbody mb_i
34.         WHERE mb_i.row <= mb.row
35.         ) < 1000000

* This source code was highlighted with Source Code Highlighter.

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