Доброй ночи,

Обращаюсь к коллективному разуму за подсказкой по одному вопросу, который мне никак не дает покоя.
Я хочу изменить представление/функцию/процедуру через динамический запрос с использованием sp_executesql с параметрами.
Как написать мой пример без параметров я знаю. Также вопрос не стоит в том зачем мне это надо.
Хочу лишь попытаться разобраться в чем проблема изменения/создания обьекта с использованием sp_executesql с параметрами.

Пример



Получаю ошибку

Если убрать параметры в sp_executesql, а переменную @obj_id сцепить через +, то все работает, как через EXEC(@cmd), так и через exec dbo.sp_executesql @cmd. Важно именно с параметром.

Из единственных логических обьяснений по этому поводу я вычитал, что sp_executesql с параметрами неявно добавляет перед DDL своё обьявление и в результате alter становится не первым в батче. Но подобного обьяснения поведения я не нашел на уровне документации. Может кому-то попадался такой случай и знает в чем причина подобного ограничения?

P.S. Прошу не цепляться за представление и писать, что мне надо UDF вместо view. Аналогичная проблема и с функциями и с процедурами.

Спасибо!

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

К примеру, вот что пишет Ицик:
авторСуществует множество методов предупреждения внедрения SQL-кода, и это тоже обширная тема. С точки зрения разработчика T-SQL, одним из наиболее предпочтительных способов является параметризация генерации и исполнения динамического SQL с использованием хранимой процедуры sp_executesql.

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


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

Если не сложно, то был бы благодарен за тезисное пояснение этого момента, почему вы считаете, что я усложняю.
А если бы можно было все-таки исполнить подобный код через параметризацию с sp_executesql, то это также было плохо? Если да, то снова почему?


P.S. Я в своем коде уже сделал реализацию подобную вашей, но через replace нужных параметров внутри строки.

Значит все-таки я уже ранее нашел ответ на мой же вопрос?


Просто я себе представлял сборку этого динамического кода по другому...
Если же выполнять обычный селект через параметризированный динамический запрос, а затем принтануть текст, то вместо параметров в тексте мы увидим значения, которые были подставлены через параметры. То есть моё представление о последовательности было следующим:
1. Создание строки с параметрами внутри
2. Передача параметров в динамический код через sp_executesql
3. Исполнение кода (в коде уже нет параметров, а только значения) через sp_executesql

Видимо пункт 2 и 3 выполняются одновременно и для его реализации идет элементарное декларирование параметров перед запросом. Выходит так? Меня сбил с толку результат принта? И ограничение для sp_executesql с параметрами являются все DDL операции?

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

Спасибо за совет. Приму к сведению и постараюсь избегать подобной практики в работе.


В моем случае, вся суть сводится к изменению обьекта через параметризованный динамический запрос.
И если в исходном примере заменить view на вашу параметризованную функцию, то ничего не изменится.
Ошибка будет прежней, только слово view поменяется на function

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

Хотя ваш пример дал осознать тот факт, что мне все ровно ведь не обойтись без конкатенации полей в моем динамическом коде.
Даже, если бы и была возможность через параметры sp_executesql толкать сами параметры, мне все ровно пришлось бы конкатенировать поля. В результате бы вышла каша из разных подходов - конкатенация и параметры.

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

За остальные советы, рекомендации и замечания я отдельно благодарен всем отписавшимся!