Чим може бути корисний динамічний sql
- і результат буде сумний, тому що операція select виконається успішно, як і операція DROP TABLE customers.
Які труднощі можуть виникнути тут?
Потрібно пам'ятати, що кожна команда відпрацює окремо, хоча на перший погляд, може здатися, що буде виконана операція конкатенації (@ sql1 + @ sql2 + @ sql3). а потім виконається загальна команда. Також потрібно пам'ятати, що накладається загальне обмеження на параметр команди EXEC в 4000 символів.
Що зміниться при використанні sp_executesql. - Розробникові простіше писати код і його налагоджувати, тому що код буде написаний практично як звичайний Sql запит.
Приклад коду з sp_executesql:
План запиту при використанні sp_executesql:
Також одна з переваг використання sp_executesql - це можливість повертати значення через OUT параметр.
Далі наведемо приклад, як ми вирішили одну з проблем в проекті з використанням dynamic sql.
Припустимо, у нас є товар (так неважливо, власне, що це: товар, анкета на посаду, персональна анкета). Сенс в тому, що кожен об'єкт має свій набір властивостей (атрибутів), який його характеризує, а їх може бути різна кількість, і вони будуть різного типу. Як зберігати в БД - це проблема архітектури.
Для клієнта потрібен був звіт, який з себе представляв n рядків на m стовпців. Де m і був наш набір атрибутів. Звіт збирався по групі об'єктів або для якогось об'єкта з групи. Але сенс залишається все той же: кожен звіт містить різну кількість стовпців для кожної групи об'єктів.
Оскільки спочатку існував зв'язок між об'єктами, то вирішення проблеми вибрали без зміни архітектури БД. На наш погляд, варіантів розв'язання проблеми може бути декілька:
- Використовувати систему звітності, наприклад, MS Sql Reporting Service. Створити матричний звіт, а в якості запиту у нас буде «простий» Select. Чому ми так не зробили? У проекті не так багато було звітів, щоб впроваджувати туди SSRS.
- Використовувати той же «простий» select і на серверній стороні вже створювати DataSet необхідної «форми». Так, так задача була вирішена спочатку, коли даних про товари було дуже мало. Як тільки даних стало досить багато, то час збору звіту стало виходить за встановлений timeout.
- Використовувати Pivot в sql. Так, відмінне рішення, коли ви знаєте, що у вас тільки ці атрибути, і нових не буде. А що робити, коли кількість атрибутів часто змінюється. І знову ж таки, для кожної групи об'єктів у нас свій набір атрибутів, ми знову повернемося до створення процедури для кожної групи об'єктів. Не дуже зручне рішення, чи не так?
- А якщо використовувати Pivot, але додати туди трохи dynamic sql. - Так, це рішення, яке має право на життя. Його ми і опишемо, як приклад використання dynamic sql ...
В основі звіту буде лежати звичайний запит: