Has anyone tried to dynamically turn a standard sql string into a parameterized query? In other words, I need to change this:
SELECT * FROM customers WHERE name = 'Adams'
to this:
SELECT * FROM customers WHERE name = @name
I've started to work with the Microsoft.Data.Schema.ScriptDom.Sql and Microsoft.Data.Schema.ScriptDom assemblies, but before I bear down on it, I was wondering if there is something already out in the wild that would do this.
SQL Server does this under the covers (and tools like ClearTrace).
One way is to use a regular expression to normalise (not exactly what you are looking for) such as this SQL CLR method based on work done by Itzik Ben-Gan and modified by Adam Machanic:
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true)]
public static SqlString sqlsig(SqlString querystring)
{
return (SqlString)Regex.Replace(
querystring.Value,
@"([\s,(=<>!](?![^\]]+[\]]))(?:(?:(?:(?:(?# expression coming
)(?:([N])?(')(?:[^']'')*('))(?# character
)(?:0x[\da-fA-F]*)(?# binary
)(?:[-+]?(?:(?:[\d]*\.[\d]*[\d]+)(?# precise number
)(?:[eE]?[\d]*)))(?# imprecise number
)(?:[~]?[-+]?(?:[\d]+))(?# integer
)(?:[nN][uU][lL][lL])(?# null
))(?:[\s]?[\+\-\*\/\%\&\\^][\s]?)?)+(?# operators
)))",
@"$1$2$3#$4");
}
but accuracy of output may not catch every possible parameterisable phrase.
I'm curious as to why you need this?
Update: As Martin mentioned, there is also the RML Utilities for SQL Server
Description of the Replay Markup Language (RML) Utilities for SQL Server
Well my knee jerk reaction for someone doing string searches/manipulation would be to use a regex to search/replace.
The hard part becomes determining what you replace with? a parameter called @Adam seems pretty weird and i'm not sure how you would know you want to replace it with @Name.
Can you describe what your end goals is? There may be a better way to do this...
