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