Search code examples
sql-servermacrosformatssmssp-executesql

Easy way to convert exec sp_executesql to a normal query?

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?

Solution

  • I am not aware of an existing Add-In that can do this. But you could create one :)

    A few regular expressions and some string concatenation and after that sell it to Vinko and other souls looking for this functionality.

    If you're feeling like diving into this, here is some information on creating an SSMS addin: http://sqlblogcasts.com/blogs/jonsayce/archive/2008/01/15/building-a-sql-server-management-studio-addin.aspx