Statements executed with
sp_executesql appear to be generally “unbound” from the procedure in which they appear. By “bind”, I simply mean “associated to the calling object”.
The goal is to make it simpler to associate statements in the Query Store:
select object_name(q.object_id) as (Statement Context) from sys.query_store_query where 1=1 and object_name(q.object_id) like 'This will be the procedure name for ''normal'' statements'
While it’s possible to embed comments in the statements that will show up in the
query_sql_text, this feels a bit extra-hackish.
Also, it seems that
sp_executesql would need some form of context binding as the dynamic SQL can access non-global temp tables in the surrounding scope: without a binding, how can SQL Server ensure the validity & stability of the temp table schema in the created plans?