sql-server - script - sp_executesql sql server
¿Una forma sencilla de convertir exec sp_executesql a una consulta normal? (8)
Conclusión: observo que esto todavía recibe un poco de atención, así que agregaré detalles aquí para ver cuál fue mi solución final.
Resulta que nada mejor que hacerlo por ti mismo. Creé una aplicación de consola simple que analizó mi procedimiento almacenado y escupió lo que quería. Al agregarlo a la lista de herramientas externas y al pasar el nombre de archivo actual como argumento, podría usar lo siguiente para eliminar y reorganizar lo que necesitaba.
En uso, agregaría un nuevo archivo sql, pegaría el sql, lo guardaría y luego ejecutaría la herramienta externa. Una vez que se completa, el IDE me pide que vuelva a cargar el archivo. Poof, no hay más procedimiento almacenado.
El manejo de excepciones y otras cosas se eliminan, y tengo en cuenta que esto puede no funcionar con cada instrucción de ejecución, por lo que tendrá que modificarlo si no satisface sus necesidades.
var text = File.ReadAllText(args[0]);
if(string.IsNullOrEmpty(text))
{
Console.WriteLine(
"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>.*)",
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();
// 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[] { '','' },
StringSplitOptions.RemoveEmptyEntries))
sb.Append("SET ").AppendLine(set);
// Add our query, removing double quotes
sb.AppendLine(match.Groups["query"].Value.Replace("''''", "''"));
File.WriteAllText(args[0], sb.ToString());
Cuando trato con consultas de depuración utilizando Profiler y SSMS, es bastante común que copie una consulta de Profiler y las pruebe en SSMS. Como uso sql parametrizado, todas mis consultas se envían como consultas exec sp_executesql.
exec sp_executesql
N''/*some query here*/'',
N''@someParameter tinyint'',
@ someParameter =2
Tomaré esto y lo convertiré en una consulta normal para facilitar la edición (inteligencia, comprobación de errores, números de línea, etc.):
DECLARE @someParameter tinyint
SET @someParameter = 2
/*some query here*/
Por supuesto, cuanto más grande y más compleja sea la consulta, más difícil será hacer esto. Y cuando va y viene varias veces, puede ser un dolor en el culo y absorber mucho tiempo.
¿Existe una manera fácil (por ejemplo, comando de macro) de convertir muh executesql en algo más conveniente?
Estaba buscando algo similar, así que uso esto en LinqPad, simplemente copie la declaración sp_executesql en el portapapeles y ejecute el código en LinqPad. Da salida a la sentencia SQL.
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();
}
}
Me enfrenté a este problema también y escribí una aplicación sencilla para resolverlo: ClipboardSqlFormatter . Esta es una aplicación de bandeja que escucha eventos de entrada del portapapeles e intenta detectar y convertir sql dinámico a sql estático.
Todo lo que necesita es copiar sql dinámico (por ejemplo, desde el perfilador de sql) y pegarlo en el editor de texto.
Por ejemplo, si el sql copiado es:
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''
entonces el sql pegado será:
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'' ) )
No tengo conocimiento de un complemento existente que pueda hacer esto. Pero podrías crear uno :)
Unas pocas expresiones regulares y un poco de concatenación de cuerdas y luego se lo venden a Vinko y otras almas en busca de esta funcionalidad.
Si tiene ganas de sumergirse en esto, aquí hay información sobre cómo crear un complemento SSMS: http://sqlblogcasts.com/blogs/jonsayce/archive/2008/01/15/building-a-sql-server-management-studio-addin.aspx
Otra solución que reemplaza los valores de los parámetros directamente en la consulta (no exactamente lo que pidió, pero podría ser útil para otros):
https://code.msdn.microsoft.com/windowsdesktop/spExecuteSql-parser-1a9cd7bc
Voy de
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''
a:
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''
lo que hace que sea más fácil de entender.
La aplicación de consola en esa página puede usarse pasando un parámetro de archivo o copiando el sp_executesql en el portapapeles, ejecutando la aplicación y luego pegando el SQL resultante del portapapeles.
Actualizar:
También se puede agregar un formateador de SQL a esa solución para facilitar la lectura:
http://www.nuget.org/packages/PoorMansTSQLFormatter/
newSql = ConvertSql(Clipboard.GetText());
var formattedSql = SqlFormattingManager.DefaultFormat(newSql);
Clipboard.SetText(formattedSql);
Pasé un poco de tiempo haciendo un guión simple que hizo esto por mí. Es un WIP, pero coloqué una página web (muy fea) delante de ella y ahora está alojada aquí si quieres intentarlo:
http://execsqlformat.herokuapp.com/
Ejemplo de entrada:
exec sp_executesql
N''SELECT * FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @level'',
N''@level tinyint'',
@level = 109;
Y la salida:
BEGIN
DECLARE @level tinyint;
SET @level = 109;
SELECT * FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @level
END
El formato de la declaración SQL real una vez que lo arranqué de la entrada se realiza mediante la API en http://sqlformat.appspot.com
Pasé un poco de tiempo y creé una pequeña modificación de las soluciones de Matt Roberts / Wangzq sin la sección DECLARE, puedes probarlo en .NET Fiddle o descargar el archivo LINQPad 5 .
Entrada:
exec sp_executesql N''UPDATE MyTable SET [Field1] = @0, [Field2] = @1'',N''@0 nvarchar(max) ,@1 int'',@0=N''String'',@1=0
Salida:
UPDATE MyTable SET [Field1] = N''String'', [Field2] = 0
Código:
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.";
}
Sql Prompt obtuvo esta característica recientemente (2017-02-06). Seleccione el texto y busque "Inline EXEC" en el menú contextual. Tengo que amar el mensaje :)