1

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 q
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?

1
3

I think that embedding comments is currently the best way to distinguish different pieces of dynamic SQL when viewed from Query Store / Extended events / Trace - unfortunately.

I've had plenty of runtime errors with dynamic sql & temp tables so I don't think it does ENSURE temp table validity & stability.

You can use @@nestlevel to see how many SPs / functions are currently in the call stack, but I think you want more information than that. You can use error_procedure() - but only in a catch block, sounds like what you want is Microsoft to add a function current_procedure() or calling_procedure() that will tell you the name of either the current routine or the routine that called this one - maybe add a request here ? :- https://feedback.azure.com/forums/908035-sql-server

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.