Easy way to convert exec sp_executesql to a normal query?

Question

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

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()
{
    ConvertSql(System.Windows.Forms.Clipboard.GetText()).Dump();
}

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);
      }
  }
  else
  {
      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)
              continue;
          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);
           sb.Append(c);
           i++;
       }

       if (s.Length - i > 0)
           sb.Append(s.Substring(i));

       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.

Input:

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

Output:

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

Code:

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";
        Console.WriteLine(ConvertSql(sql));
    }

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

https://code.msdn.microsoft.com/windowsdesktop/spExecuteSql-parser-1a9cd7bc

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'

to:

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.

Update:

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

http://www.nuget.org/packages/PoorMansTSQLFormatter/

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

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

You can use this Azur data studio extension. it based on @Matt Roberts repo. https://github.com/PejmanNik/sqlops-spexecutesql-to-sql/releases/tag/0.0.1

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