StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Whilst not perfect, here's something I knocked up something for TSQL - could be easily tweaked for other flavours... If nothing else it will give you a start point for your own improvements :)</p> <p>This does an OK job on data types and output parameters etc similar to using "execute stored procedure" in SSMS. We mostly used SPs so the "text" command doesn't account for parameters etc</p> <pre><code>public static String ParameterValueForSQL(this SqlParameter sp) { String retval = ""; switch (sp.SqlDbType) { case SqlDbType.Char: case SqlDbType.NChar: case SqlDbType.NText: case SqlDbType.NVarChar: case SqlDbType.Text: case SqlDbType.Time: case SqlDbType.VarChar: case SqlDbType.Xml: case SqlDbType.Date: case SqlDbType.DateTime: case SqlDbType.DateTime2: case SqlDbType.DateTimeOffset: retval = "'" + sp.Value.ToString().Replace("'", "''") + "'"; break; case SqlDbType.Bit: retval = (sp.Value.ToBooleanOrDefault(false)) ? "1" : "0"; break; default: retval = sp.Value.ToString().Replace("'", "''"); break; } return retval; } public static String CommandAsSql(this SqlCommand sc) { StringBuilder sql = new StringBuilder(); Boolean FirstParam = true; sql.AppendLine("use " + sc.Connection.Database + ";"); switch (sc.CommandType) { case CommandType.StoredProcedure: sql.AppendLine("declare @return_value int;"); foreach (SqlParameter sp in sc.Parameters) { if ((sp.Direction == ParameterDirection.InputOutput) || (sp.Direction == ParameterDirection.Output)) { sql.Append("declare " + sp.ParameterName + "\t" + sp.SqlDbType.ToString() + "\t= "); sql.AppendLine(((sp.Direction == ParameterDirection.Output) ? "null" : sp.ParameterValueForSQL()) + ";"); } } sql.AppendLine("exec [" + sc.CommandText + "]"); foreach (SqlParameter sp in sc.Parameters) { if (sp.Direction != ParameterDirection.ReturnValue) { sql.Append((FirstParam) ? "\t" : "\t, "); if (FirstParam) FirstParam = false; if (sp.Direction == ParameterDirection.Input) sql.AppendLine(sp.ParameterName + " = " + sp.ParameterValueForSQL()); else sql.AppendLine(sp.ParameterName + " = " + sp.ParameterName + " output"); } } sql.AppendLine(";"); sql.AppendLine("select 'Return Value' = convert(varchar, @return_value);"); foreach (SqlParameter sp in sc.Parameters) { if ((sp.Direction == ParameterDirection.InputOutput) || (sp.Direction == ParameterDirection.Output)) { sql.AppendLine("select '" + sp.ParameterName + "' = convert(varchar, " + sp.ParameterName + ");"); } } break; case CommandType.Text: sql.AppendLine(sc.CommandText); break; } return sql.ToString(); } </code></pre> <p>this generates output along these lines...</p> <pre><code>use dbMyDatabase; declare @return_value int; declare @OutTotalRows BigInt = null; exec [spMyStoredProc] @InEmployeeID = 1000686 , @InPageSize = 20 , @InPage = 1 , @OutTotalRows = @OutTotalRows output ; select 'Return Value' = convert(varchar, @return_value); select '@OutTotalRows' = convert(varchar, @OutTotalRows); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload