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
- Can anyone confirm definitively that my conclusions about the extra statements are correct/incorrect?
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 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. The result I got was this:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'PROCEDURE'.
This was very confusing, but after wrangling with it for quite some time, I finally figured out the problem:
sp_executesql binds parameters to dynamic SQL by secretly adding a
DECLARE statement to the top before executing. Since
CREATE PROCEDURE must be the only statement in the batch, but there is now an extra statement before the
CREATE PROCEDURE line, it throws an error. It does say
Line 1--which further misled me--but this is obviously tweaked by the engine so people don't get confused about the line numbers of their own script when dealing with errors.
The solution to the problem was to detect which type of object was being worked with and NOT pass in the
@Debug parameter so script that must have no other statements works okay. A quick change did the job:
IF @ScriptType IN ('Procedure', 'View', 'Function') BEGIN EXEC sp_executesql @CreateSQL; END ELSE BEGIN EXEC sp_executesql @CreateSQL, N'@Debug bit', @Debug; END;
I could also have nested my dynamic SQL one level deeper, to create the procedure inside dynamic sql (again, inside the script in the table) but that was a less optimal solution in my case.
I suspect that using
OUTPUT variables with
sp_executesql would also add one or more statements to the end of the script to enable the engine to capture them, most likely in a
SELECT statement that is silently swallowed up.