Задача: в stored procedure динамически формировать SQL для получения данных. Динамическими должны быть все части: названия таблиц, количество условий, названия колонок. В идеале нужно просто составлять строку, а потом выполнять запрос из нее, при этом вообще хорошо, если удастся обеспечить параметризацию.

Решение: использование sp_executesql
Сразу на примере, ситуация.

Пусть у нас есть несколько сущностей в системе. Есть пользователи, есть записи в блоге, есть статьи.
Все они хранятся в трех разных таблицах account, blog_post, content_page (primary keys: account_id, blog_post_id, content_page_id).
В каждой из этих таблиц есть, соответственно, поля acc_created, bp_created, cp_created. И представьте теперь, что в одной из sp нам необходимо за заданному id и типу сущности получить creation date. Можно написать 3 запроса — легко, ну а если сущностей, скажем, 50? Тогда простое решение становится уж очень некрасивым.

На помощь приходит динамический sql.
Attention. Я знаю про перфоманс и про все остальное, я знаю что 50 отдельных запросов будут быстрее. Речь не об этом, а о ситуации, когда принято решение, что делать нужно так.

set @sql =     N'select @created =  ' + @pspAbbrev + '_created from ' + @pspFull + ' where ' + @pspFull + '_ID = @id;';

set @params =  N'@id     integer, ' +
	       N'@created   datetime OUTPUT';
			   
exec sp_executesql @sql, @params, @id = @id, @created = @created OUTPUT

Прежде всего, нам необходимо определить @pspAbbrev (например, ‘usr’) и @pspFull(например, ‘user’).
После чего сформировать @sql, определить input-output параметры, вызвать sp_executesql и далее пользоваться полученным значением из переменной @created.