sp_executesql adds statements to executed dynamic script?

The Question:

As far as I can tell, sp_executesql adds statements to the beginning of submitted dynamic SQL script. But, a SQL Profiler trace does not capture the extra statements, and neither does DBCC OUTPUTBUFFER. So:

• Is there any way to see the extra statements added to submitted dynamic SQL batches by sp_executesql?

• Can anyone confirm definitively that my conclusions about the extra statements are correct/incorrect?

Background

I have a database where some objects (views, synonyms, SPs) are rewritten based on data in a Script table. If the database is moved to another server, a stored procedure loops through the rows of the Script table, replaces certain key values in the supplied SQL script with those defined for the new server context, and runs the script.

Everything was working fine until I made a few tweaks to add support for scripting permissions through this same mechanism. The database integrates with a vendor's product, and in each environment the vendor's database can have a different user that must be given permission to a particular view in my database for reporting purposes. So, I have to query for that user (from the vendor's database) then use that name to create the user in my database if it doesn't exist and finally grant SELECT permission. This required more lengthy scripting and doing dynamic-sql inside of dynamic-sql, so I wanted to pass in my outer script's @Debug parameter so I could see the extra script that was being generated and confirm its correctness before trying to execute it.

Other than changing what object types could be scripted and making the DROP script optional, the only material change I made to accommodate the @Debug parameter was to change this:

EXEC (@CreateSQL);

to this:

EXEC sp_executesql @CreateSQL, N'@Debug bit', @Debug;

Then I ran into a problem: the one stored procedure in my Script table could no longer be created, though the DROP just before it still worked okay.

Prior to the sp_executesql you should see an sp_prepare statement in Profiler that would give more insight: http://msdn.microsoft.com/en-us/library/ff848808(v=sql.110).aspx

Sometimes this can be hard to find, but was made a bit easier with extended events in 2008R2 - specifically causality tracking.

http://msdn.microsoft.com/en-us/library/bb630284.aspx

StackExchange Database Administrators Q#43262, answer score: 4