Easy way to convert exec sp_executesql to a normal query?

user1228 picture user1228 · Jul 16, 2009 · Viewed 9.3k times · Source

When dealing with debugging queries using Profiler and SSMS, its pretty common for me to copy a query from Profiler and test them in SSMS. Because I use parameterized sql, my queries are all sent as exec sp_executesql queries.

exec sp_executesql 
N'/*some query here*/', 
N'@someParameter tinyint',
@ someParameter =2

I'll take this and convert it into a normal query for ease of editing (intellisense, error checking, line numbers, etc):

DECLARE @someParameter tinyint
SET @someParameter = 2

/*some query here*/

Of course, the bigger and more complex the query, the harder to do this. And when you're going back and forth multiple times, it can be a pain in the ass and soak up lots of time.

Is there an easy (e.g., macro command) way to convert muh executesql into something more convenient?

Answer

Matt Roberts picture Matt Roberts · Dec 20, 2011

I spent a little time making an simple script that did this for me. It's a WIP, but I stuck a (very ugly) webpage in front of it and it's now hosted here if you want to try it:

http://execsqlformat.herokuapp.com/

Sample input:

exec sp_executesql 
          N'SELECT * FROM AdventureWorks.HumanResources.Employee 
          WHERE ManagerID = @level',
          N'@level tinyint',
          @level = 109;

And the output:

BEGIN
DECLARE @level tinyint;

SET @level = 109;

SELECT * FROM AdventureWorks.HumanResources.Employee  
          WHERE ManagerID = @level
END

The formatting of the actual SQL statement once I've plucked it from the input is done using the API at http://sqlformat.appspot.com