Easy way to convert exec sp_executesql to a normal query?

Question

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?

Reber

+1 I'd love that as well – Fingerstall

You should consider posting your eventual solution as an answer... – Diarist

Answer 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

Hyder

Answer 2

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

Gorgonzola

Hi Matt, I am very interested in how you used the api to convert sp_executesql, can you share your code? Thanks! – Oates

Bunged it into a git repo for you to enjoy ;) github.com/mattwoberts/execsqlformat – Gorgonzola

Thanks! It is good to know you are using regex to parse the input first. – Oates

github.com/wangzq/convert-sp_executesql/blob/master/… – Oates

Not to look a gift-horse in the mouth, but the "clipboard" button copies the text with line-feeds, instead of CR/LF, which makes it impossible to paste into anything on Windows, even Notepad++. User has to copy select the HTML and copy. It would have been better just to make that control an HTML text area so that you could Ctrl+A to select-all then copy. Not that I'm complaining or anything. :) – Triarchy

Thanks for the time you put on the tool. There is another little problem: It does not correctly handle parameter types like decimal(x,y). It puts a DECLARE after the comma. – Redwood

Just write an externtion based on your repo for Azure Data Storage github.com/PejmanNik/sqlops-spexecutesql-to-sql/releases/tag/… – Milt

The links in the answer are not valid anymore. Does anyone have the new link for this tool? – Wallah

Answer 3

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.";
    }
}

Landan

Answer 4

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();
   }
}

Greenwich

Answer 5

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

Jaynes

Answer 6

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

Milt

Answer 7

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

Milreis

Answer 8

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.

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.

using System;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;

class Program
{
    const string query = "query";
    const string decls = "decls";
    const string sets = "sets";

    static void Main(string[] args)
    {
        try
        {
            var text = File.ReadAllText(args[0]);
            if (string.IsNullOrEmpty(text))
            {
                Console.WriteLine("File is empty.");
            }

            var regex = new Regex(
                @"exec sp_executesql N'(?<" + query + ">.*)',N'(?<" + decls + ">[^']*)',(?<" + sets + ">.*)",
                RegexOptions.Singleline);
            var match = regex.Match(text);

            if (!match.Success || match.Groups.Count != 4)
            {
                Console.WriteLine("Didn't capture that one.");
                Console.Read();
                return;
            }

            var sb = new StringBuilder();
            sb.Append("DECLARE ").AppendLine(match.Groups[decls].Value);
            foreach (var set in match.Groups[sets].Value
                         .Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
                sb.Append("SET ").AppendLine(set);
            sb.AppendLine(match.Groups[query].Value.Replace("''", "'"));
            File.WriteAllText(args[0], sb.ToString());
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
            Console.Read();
        }
    }
}

Kennithkennon

Answer 9

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" ,"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" = 6815463 AND ( "doc"."StateID" <> 5 AND "ContainerID.jrn"."Name" <> 'Hidden journal' ) )

Cheesy

Answer 10

Here is simple UI that i use to inspect NHibernate queries. Some regex, parsing and sqlformat.org API to beautify sql is used.

<html>
<head>
    <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
    <script type="text/javascript">
        $(function(){
            $("#btn-format").on("click", () =>{
                var insql = $("#textarea-in").val();        

                var regex = new RegExp("exec sp_executesql N'(?<command>.+?(?='(,N'$)?))'(,\s*N'(?<types>.+?(?=',))',\s*(?<vals>.+))?");
                var groups = insql.replace(/\n|\r/g, "").match(regex).groups;

                var outsql = "";
                if (groups.types)
                {
                    var types = groups.types.match(/@[^\s]+ \w+(\([\w\d,]+\))?/g);
                    for (const typeDeclaration of types) {
                        outsql = outsql + 'declare ' +  typeDeclaration + '\n';
                    }
                    outsql = outsql + '\n';
                    for (const setVal of groups.vals.split(',')) {
                        outsql = outsql + 'set ' +  setVal + '\n';
                    }
                    outsql = outsql + '\n';
                }
                $.ajax({
                    url: 'https://sqlformat.org/api/v1/format',
                    type: 'POST',
                    dataType: 'json',
                    crossDomain: true,
                    data: {
                        sql: groups.command, reindent: 1
                    },
                    success: (data) => {
                        outsql = outsql + data.result;
                        $("#textarea-out").val(outsql);
                    },
                    error: () =>{
                        outsql = outsql + '-- No format happened. See browser console for details \n';
                        outsql = outsql + groups.command;
                        $("#textarea-out").val(outsql);
                    }
                });        
            })    
        });
    </script>        
</head>
<body>
    <textarea id="textarea-in" style="width: 100%; height: 48%;" class="form-control" placeholder="type 'exec sp_executesql...' here"></textarea>
    <br/>
    <button id="btn-format">Format</button>
    <br/>
    <textarea id="textarea-out" style="width: 100%; height: 48%;" class="form-control"></textarea>
</body>

Test in Fiddle

Note: Will not work, if you have single quotes in query

Timmons

Answer 11

I have improved one of previous answers particularly refining regular expression in order to support queries without parameters. Here is my option in form of PowerShell script, which uses Windows clipboard both as input and as output:

$regex = "(?s)^exec sp_executesql N'(?<query>.*?[^'])'(?:,N'(?<decls>.*?[^'])',(?<sets>.*))?$"

$inputText = [string](Get-Clipboard)
if (!($inputText -match $regex)) {
    return
}

$resultBuilder = [System.Text.StringBuilder]::new()
if ($matches["decls"]) {
    $resultBuilder.AppendLine("DECLARE $($matches["decls"])")
    $matches["sets"].Split(',') | ForEach-Object {
        $resultBuilder.AppendLine("SET $_");
    }
}
$resultBuilder.AppendLine($matches["query"].Replace("''", "'"));
Set-Clipboard $resultBuilder.ToString()

Mandiemandingo