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):

  • Does a SELECT query always return rows in the same order? Table with clustered index
  • string concat for a title forename surname combination in sql using isnull to check values
  • Combine stored procedure and query in T-SQL
  • Incorrect syntax near '(' and near '='
  • SSMS stopped loading my stored procedures after update
  • Reasons for SQL differences
  • 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?

    8 Solutions collect form web for “Easy way to convert exec sp_executesql to a normal query?”

    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:

    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:


    Sample input:

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

    And the output:

    DECLARE @level tinyint;
    SET @level = 109;
    SELECT * FROM AdventureWorks.HumanResources.Employee  
              WHERE ManagerID = @level

    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

    I was looking for something similar so I use this in LinqPad, just copy sp_executesql statement to the clipboard and run the code in LinqPad. It outputs the SQL statement.

    void Main()
    private static string ConvertSql(string origSql)
      string tmp = origSql.Replace("''", "~~");       
      string baseSql;
      string paramTypes;
      string paramData = "";
      int i0 = tmp.IndexOf("'") + 1;
      int i1 = tmp.IndexOf("'", i0);
      if (i1 > 0)
          baseSql = tmp.Substring(i0, i1 - i0); 
          i0 = tmp.IndexOf("'", i1 + 1);
          i1 = tmp.IndexOf("'", i0 + 1);
          if (i0 > 0 && i1 > 0)
              paramTypes = tmp.Substring(i0 + 1, i1 - i0 - 1);
              paramData = tmp.Substring(i1 + 1);
          throw new Exception("Cannot identify SQL statement in first parameter");
      baseSql = baseSql.Replace("~~", "'");  
      if (!String.IsNullOrEmpty(paramData))  
          string[] paramList = paramData.Split(",".ToCharArray());
          foreach (string paramValue in paramList)
              int iEq = paramValue.IndexOf("=");
              if (iEq < 0)
              string pName = paramValue.Substring(0, iEq).Trim();
              string pVal = paramValue.Substring(iEq + 1).Trim();
              baseSql = baseSql.ReplaceWholeWord(pName, pVal);
      return baseSql;
    public static class StringExtensionsMethods
       /// <summary>
       /// Replaces the whole word.
       /// </summary>
       /// <param name="s">The s.</param>
       /// <param name="word">The word.</param>
       /// <param name="replacement">The replacement.</param>
       /// <returns>String.</returns>
       public static String ReplaceWholeWord(this String s, String word, String replacement)
           var firstLetter = word[0];
           var sb = new StringBuilder();
           var previousWasLetterOrDigit = false;
           var i = 0;
           while (i < s.Length - word.Length + 1)
               var wordFound = false;
               var c = s[i];
               if (c == firstLetter)
                   if (!previousWasLetterOrDigit)
                       if (s.Substring(i, word.Length).Equals(word))
                           wordFound = true;
                           var wholeWordFound = true;
                           if (s.Length > i + word.Length)
                               if (Char.IsLetterOrDigit(s[i + word.Length]))
                                   wholeWordFound = false;
                           sb.Append(wholeWordFound ? replacement : word);
                           i += word.Length;
               if (wordFound) continue;
               previousWasLetterOrDigit = Char.IsLetterOrDigit(c);
           if (s.Length - i > 0)
           return sb.ToString();

    I spent a little time and created a small modification of Matt Roberts / Wangzq solutions without DECLAREs section, you can try it on .NET Fiddle or download LINQPad 5 file.


    exec sp_executesql N'UPDATE MyTable SET [Field1] = @0, [Field2] = @1',N'@0 nvarchar(max) ,@1 int',@0=N'String',@1=0


    UPDATE MyTable SET [Field1] = N'String', [Field2] = 0


    using System;
    using System.Linq;
    using System.Text.RegularExpressions;
    public class Program
        public static void Main()
            var sql = @"
    exec sp_executesql N'UPDATE MyTable SET [Field1] = @0, [Field2] = @1',N'@0 nvarchar(max) ,@1 int',@0=N'String',@1=0
        public static string ConvertSql(string origSql)
            var re = new Regex(@"exec*\s*sp_executesql\s+N'([\s\S]*)',\s*N'(@[\s\S]*?)',\s*([\s\S]*)", RegexOptions.IgnoreCase); // 1: the sql, 2: the declare, 3: the setting
            var match = re.Match(origSql);
            if (match.Success)
                var sql = match.Groups[1].Value.Replace("''", "'");
                //var declare = match.Groups[2].Value;
                var setting = match.Groups[3].Value + ',';
                // to deal with comma or single quote in variable values, we can use the variable name to split
            var re2 = new Regex(@"@[^',]*?\s*=");
                var variables = re2.Matches(setting).Cast<Match>().Select(m => m.Value).ToArray();
            var values = re2.Split(setting).Where(s=>!string.IsNullOrWhiteSpace(s)).Select(m => m.Trim(',').Trim().Trim(';')).ToArray();
                for (int i = variables.Length-1; i>=0; i--)
                sql = Regex.Replace(sql, "(" + variables[i].Replace("=", "")+")", values[i], RegexOptions.Singleline);
                return sql;     
            return @"Unknown sql query format.";

    Another solution which replaces the parameter values directly in the query
    (not exactly what you asked for but it might prove useful to others):


    I goes from:

    exec sp_executesql N'UPDATE Task SET Status = @p0, Updated = @p1 WHERE Id = @p2 AND Status = @p3 AND Updated = @p4',N'@p0 int,@p1 datetime,@p2 int,@p3 int,@p4 datetime',@p0=1,@p1='2015-02-07 21:36:30.313',@p2=173990,@p3=2,@p4='2015-02-07 21:35:32.830'


    UPDATE Task SET Status = 1, Updated = '2015-02-07 21:36:30.313' WHERE Id = 173990 AND Status = 2 AND Updated = '2015-02-07 21:35:32.830'

    which makes it easier to understand.

    The console application on that page can be used by passing a file parameter or copying the sp_executesql in the clipboard, running the app and then pasting the resulting SQL from the clipboard.


    An SQL formatter can also be added to that solution for easier readability:


    newSql = ConvertSql(Clipboard.GetText());
    var formattedSql = SqlFormattingManager.DefaultFormat(newSql);

    Sql Prompt got this feature recently (2017-02-06). Select the text and look for “Inline EXEC” in the context menu. Gotta love Prompt 🙂

    I faced with this problem too and wrote simple application for solving it – ClipboardSqlFormatter. This is a tray application that listens clipboard input events and tries to detect and convert dynamic sql to static sql.

    Anything you need is to copy dynamic sql (from sql profiler for example) and paste to text editor – pasted sql will be a static sql 🙂

    For example, if copied sql is:

    exec sp_executesql N' SELECT "obj"."CreateDateTime", "obj"."LastEditDateTime" FROM LDERC
    "doc" INNER JOIN LDObject "obj" ON ("doc"."ID" = "obj"."ID") LEFT OUTER JOIN LDJournal
    "ContainerID.jrn" ON ("doc"."JournalID" = "ContainerID.jrn"."ID") WHERE ( "doc"."ID"
    = @V0 AND ( "doc"."StateID" <> 5 AND "ContainerID.jrn"."Name" <> ''Hidden journal''
    ) ) ',N'@V0 bigint',@V0=6815463'

    then pasted sql will be:

    SELECT "obj"."CreateDateTime"
    FROM LDERC "doc"
    INNER JOIN LDObject "obj" ON ("doc"."ID" = "obj"."ID")
    LEFT OUTER JOIN LDJournal "ContainerID.jrn" ON ("doc"."JournalID" = "ContainerID.jrn"."ID")
    WHERE (
    "doc"."ID" = 6815463
    AND (
    "doc"."StateID" <> 5
    AND "ContainerID.jrn"."Name" <> 'Hidden journal'

    Conclusion: I note this still gets a little attention so I’ll add details here for what my eventual solution was.

    It turns out that nothing beats doing it for yourself. I created a simple console app that parsed my stored procedure and spit out what I wanted. By adding it to the list of external tools, and passing the current filename as an argument, I could use the following to strip out and rearrange what I needed.

    In use, I’d add a new sql file, paste in the sql, save it, then run the external tool. After it completes, the IDE asks me to reload the file. Poof, no more stored procedure.

    Exception handling and other stuff is stripped out, and I do note that this may not work with every executesql statement, so you’ll have to modify if it does not meet your needs.

    var text = File.ReadAllText(args[0]);
            "File is empty; try saving it before using the hillbilly stored procedure decoder");
    var regex = new Regex(
        @"exec sp_executesql N'(?<query>.*)',N'(?<decls>.*)',(?<sets>.*)",     
    var match = regex.Match(text);
    if(!match.Success || match.Groups.Count != 4)
        Console.WriteLine("Didn't capture that one.");
    var sb = new StringBuilder();
    // declares go on top
    sb.Append("DECLARE ").AppendLine(match.Groups["decls"].Value);
    // split out our sets, add them one line at a time
    foreach(var set in match.Groups["sets"]
                       .Value.Split(new char[] { ',' }, 
        sb.Append("SET ").AppendLine(set);
    // Add our query, removing double quotes
    sb.AppendLine(match.Groups["query"].Value.Replace("''", "'"));
    File.WriteAllText(args[0], sb.ToString());
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.