Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. J it only takes a minute:
The statement is
SELECT * FROM tableA WHERE x = ?
and the parameter is inserted via java.sql.PreparedStatement 'stmt'
stmt.setString(1, y); // y may be null
If y is null, the statement returns no rows in every case because x = null is always false (should be x IS NULL).
One solution would be
SELECT * FROM tableA WHERE x = ? OR (x IS NULL AND ? IS NULL)
But then i have to set the same parameter twice. Is there a better solution?
219k37488592
3,22172856
I've always done it the way you show in your question.
Setting the same parameter twice is not such a huge hardship, is it?
SELECT * FROM tableA WHERE x = ? OR (x IS NULL AND ? IS NULL);
102k30232342
would just use 2 different statements:
Statement 1:
SELECT * FROM tableA WHERE x is NULL
Statement 2:
SELECT * FROM tableA WHERE x = ?
You can check your variable and build the proper statement depending on the condition. I think this makes the code much clearer and easier to understand.
By the way, why not use stored procedures? Then you can handle all this NULL logic in the SP and you can simplify things on the front end call.
31.2k788117
There is a quite unknown ANSI-SQL operator IS DISTINCT FROM that handles NULL values. It can be used like that:
SELECT * FROM tableA WHERE x NOT IS DISTINCT FROM ?
So only one parameter has to be set. Unfortunately, this is not supported by MS SQL Server (2008).
Another solution could be, if there is a value that is and will be never used ('XXX'):
SELECT * FROM tableA WHERE COALESCE(x, 'XXX') = COALESCE(?, 'XXX')
3,22172856
If you use for instance mysql you could probably do something like:
select * from mytable where ifnull(mycolumn,'') = ?;
Then yo could do:
stmt.setString(1, foo == null ? "" : foo);
You would have to check your explain plan to see if it improves your performance. It though would mean that the empty string is equal to null, so it is not granted it would fit your needs.
4,88631120
Your Answer
Sign up or
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Post as a guest
By posting your answer, you agree to the
Not the answer you're looking for?
Browse other questions tagged
Stack Overflow works best with JavaScript enabledStack Overflow is a community of 4.7 million programmers, just like you, helping each other. J it only takes a minute:
I have the following code:
Using cmd As SqlCommand = Connection.CreateCommand
mandText = "UPDATE someTable SET Value = @Value"
mandText &= " WHERE Id = @Id"
cmd.Parameters.AddWithValue("@Id", 1234)
cmd.Parameters.AddWithValue("@Value", "myValue")
cmd.ExecuteNonQuery
I wonder if there is any way to get the final SQL-Statment as a String, which should look like this:
UPDATE someTable SET Value = "myValue" WHERE Id = 1234
If anyone wonders why I would do this:
for logging (failed) statements
for having the possibility to copy & paste it to the Enterprise Manager for testing purposes
2,07421429
For logging purposes, I'm afraid there's no nicer way of doing this but to construct the string yourself:
string query = mandT
foreach (SqlParameter p in cmd.Parameters)
query = query.Replace(p.ParameterName, p.Value.ToString());
Sorry, I forgot..
p.Value.ToString() should do the job.
15.6k95582
18.5k74371
Did you find this question interesting? Try our newsletter
Sign up for our newsletter and get our top new questions delivered to your inbox ().
Subscribed!
Success! Please click the link in the confirmation email to activate your subscription.
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 :)
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
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("'", "''") + "'";
case SqlDbType.Bit:
retval = (sp.Value.ToBooleanOrDefault(false)) ? "1" : "0";
retval = sp.Value.ToString().Replace("'", "''");
public static String CommandAsSql(this SqlCommand sc)
StringBuilder sql = new StringBuilder();
Boolean FirstParam =
sql.AppendLine("use " + sc.Connection.Database + ";");
switch (sc.CommandType)
case CommandType.StoredProcedure:
sql.AppendLine("declare @return_");
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 =
if (sp.Direction == ParameterDirection.Input)
sql.AppendLine(sp.ParameterName + " = " + sp.ParameterValueForSQL());
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 + ");");
case CommandType.Text:
sql.mandText);
return sql.ToString();
this generates output along these lines...
declare @return_
declare @OutTotalRows
exec [spMyStoredProc]
@InEmployeeID = 1000686
, @InPageSize = 20
, @InPage = 1
, @OutTotalRows = @OutTotalRows output
select 'Return Value' = convert(varchar, @return_value);
select '@OutTotalRows' = convert(varchar, @OutTotalRows);
You can't, because it does not generate any SQL.
The parameterized query (the one in CommandText) is sent to the SQL Server as the equivalent of a prepared statement. When you execute the command, the parameters and the query text are treated separately. At no point in time a complete SQL string is generated.
You can use SQL Profiler to take a look behind the scenes.
187k35306435
I needed a similar command to string transformer to allow for more verbose logging, so I wrote this one.
It will produce the text needed to re-execute the command in a new session including output parameters and structured parameters.
It is lightly tested, but caveat emptor.
SqlCommand cmd = new SqlCommand("GetEntity", con);
cmd.Parameters.AddWithValue("@foobar", 1);
cmd.Parameters.Add(new SqlParameter(){
ParameterName = "@outParam",
Direction = ParameterDirection.Output,
SqlDbType = System.Data.SqlDbType.Int
cmd.Parameters.Add(new SqlParameter(){
Direction = ParameterDirection.ReturnValue
mandType = CommandType.StoredP
will produce:
-- BEGIN COMMAND
DECLARE @foobar INT = 1;
DECLARE @outParam INT = NULL;
DECLARE @returnValue INT;
-- END PARAMS
EXEC @returnValue = GetEntity @foobar = @foobar, @outParam = @outParam OUTPUT
-- RESULTS
SELECT 1 as Executed, @returnValue as ReturnValue, @outParam as [@outParam];
-- END COMMAND
Implementation:
public class SqlCommandDumper
public static string GetCommandText(SqlCommand sqc)
StringBuilder sbCommandText = new StringBuilder();
sbCommandText.AppendLine("-- BEGIN COMMAND");
for (int i = 0; i & sqc.Parameters.C i++)
logParameterToSqlBatch(sqc.Parameters[i], sbCommandText);
sbCommandText.AppendLine("-- END PARAMS");
// command
if (mandType == CommandType.StoredProcedure)
sbCommandText.Append("EXEC ");
bool hasReturnValue =
for (int i = 0; i & sqc.Parameters.C i++)
if (sqc.Parameters[i].Direction == ParameterDirection.ReturnValue)
hasReturnValue =
if (hasReturnValue)
sbCommandText.Append("@returnValue = ");
sbCommandText.mandText);
bool hasPrev =
for (int i = 0; i & sqc.Parameters.C i++)
var cParam = sqc.Parameters[i];
if (cParam.Direction != ParameterDirection.ReturnValue)
if (hasPrev)
sbCommandText.Append(", ");
sbCommandText.Append(cParam.ParameterName);
sbCommandText.Append(" = ");
sbCommandText.Append(cParam.ParameterName);
if (cParam.Direction.HasFlag(ParameterDirection.Output))
sbCommandText.Append(" OUTPUT");
sbCommandText.mandText);
sbCommandText.AppendLine("-- RESULTS");
sbCommandText.Append("SELECT 1 as Executed");
for (int i = 0; i & sqc.Parameters.C i++)
var cParam = sqc.Parameters[i];
if (cParam.Direction == ParameterDirection.ReturnValue)
sbCommandText.Append(", @returnValue as ReturnValue");
else if (cParam.Direction.HasFlag(ParameterDirection.Output))
sbCommandText.Append(", ");
sbCommandText.Append(cParam.ParameterName);
sbCommandText.Append(" as [");
sbCommandText.Append(cParam.ParameterName);
sbCommandText.Append(']');
sbCommandText.AppendLine(";");
sbCommandText.AppendLine("-- END COMMAND");
return sbCommandText.ToString();
private static void logParameterToSqlBatch(SqlParameter param, StringBuilder sbCommandText)
sbCommandText.Append("DECLARE ");
if (param.Direction == ParameterDirection.ReturnValue)
sbCommandText.AppendLine("@returnValue INT;");
sbCommandText.Append(param.ParameterName);
sbCommandText.Append(' ');
if (param.SqlDbType != SqlDbType.Structured)
logParameterType(param, sbCommandText);
sbCommandText.Append(" = ");
logQuotedParameterValue(param.Value, sbCommandText);
sbCommandText.AppendLine(";");
logStructuredParameter(param, sbCommandText);
private static void logStructuredParameter(SqlParameter param, StringBuilder sbCommandText)
sbCommandText.AppendLine(" {List Type};");
var dataTable = (DataTable)param.V
for (int rowNo = 0; rowNo & dataTable.Rows.C rowNo++)
sbCommandText.Append("INSERT INTO ");
sbCommandText.Append(param.ParameterName);
sbCommandText.Append(" VALUES (");
bool hasPrev =
for (int colNo = 0; colNo & dataTable.Columns.C colNo++)
if (hasPrev)
sbCommandText.Append(", ");
logQuotedParameterValue(dataTable.Rows[rowNo].ItemArray[colNo], sbCommandText);
sbCommandText.AppendLine(");");
const string DATETIME_FORMAT_ROUNDTRIP = "o";
private static void logQuotedParameterValue(object value, StringBuilder sbCommandText)
if (value == null)
sbCommandText.Append("NULL");
value = unboxNullable(value);
if (value is string
|| value is char
|| value is char[]
|| value is System.Xml.Linq.XElement
|| value is System.Xml.Linq.XDocument)
sbCommandText.Append('\'');
sbCommandText.Append(value.ToString().Replace("'", "''"));
sbCommandText.Append('\'');
else if (value is bool)
// True -& 1, False -& 0
sbCommandText.Append(Convert.ToInt32(value));
else if (value is sbyte
|| value is byte
|| value is short
|| value is ushort
|| value is int
|| value is uint
|| value is long
|| value is ulong
|| value is float
|| value is double
|| value is decimal)
sbCommandText.Append(value.ToString());
else if (value is DateTime)
// SQL Server only supports ISO8601 with 3 digit precision on datetime,
// datetime2 (&= SQL Server 2008) parses the .net format, and will
// implicitly cast down to datetime.
// Alternatively, use the format string "yyyy'-'MM'-'dd'T'HH':'mm':'ss'.'fffK"
// to match SQL server parsing
sbCommandText.Append("CAST('");
sbCommandText.Append(((DateTime)value).ToString(DATETIME_FORMAT_ROUNDTRIP));
sbCommandText.Append("' as datetime2)");
else if (value is DateTimeOffset)
sbCommandText.Append('\'');
sbCommandText.Append(((DateTimeOffset)value).ToString(DATETIME_FORMAT_ROUNDTRIP));
sbCommandText.Append('\'');
else if (value is Guid)
sbCommandText.Append('\'');
sbCommandText.Append(((Guid)value).ToString());
sbCommandText.Append('\'');
else if (value is byte[])
var data = (byte[])
if (data.Length == 0)
sbCommandText.Append("NULL");
sbCommandText.Append("0x");
for (int i = 0; i & data.L i++)
sbCommandText.Append(data[i].ToString("h2"));
sbCommandText.Append("/* UNKNOWN DATATYPE: ");
sbCommandText.Append(value.GetType().ToString());
sbCommandText.Append(" *" + "/ '");
sbCommandText.Append(value.ToString());
sbCommandText.Append('\'');
catch (Exception ex)
sbCommandText.AppendLine("/* Exception occurred while converting parameter: ");
sbCommandText.AppendLine(ex.ToString());
sbCommandText.AppendLine("*/");
private static object unboxNullable(object value)
var typeOriginal = value.GetType();
if (typeOriginal.IsGenericType
&& typeOriginal.GetGenericTypeDefinition() == typeof(Nullable&&))
// generic value, unboxing needed
return typeOriginal.InvokeMember("GetValueOrDefault",
System.Reflection.BindingFlags.Public |
System.Reflection.BindingFlags.Instance |
System.Reflection.BindingFlags.InvokeMethod,
null, value, null);
private static void logParameterType(SqlParameter param, StringBuilder sbCommandText)
switch (param.SqlDbType)
// variable length
case SqlDbType.Char:
case SqlDbType.NChar:
case SqlDbType.Binary:
sbCommandText.Append(param.SqlDbType.ToString().ToUpper());
sbCommandText.Append('(');
sbCommandText.Append(param.Size);
sbCommandText.Append(')');
case SqlDbType.VarChar:
case SqlDbType.NVarChar:
case SqlDbType.VarBinary:
sbCommandText.Append(param.SqlDbType.ToString().ToUpper());
sbCommandText.Append("(MAX /* Specified as ");
sbCommandText.Append(param.Size);
sbCommandText.Append(" */)");
// fixed length
case SqlDbType.Text:
case SqlDbType.NText:
case SqlDbType.Bit:
case SqlDbType.TinyInt:
case SqlDbType.SmallInt:
case SqlDbType.Int:
case SqlDbType.BigInt:
case SqlDbType.SmallMoney:
case SqlDbType.Money:
case SqlDbType.Decimal:
case SqlDbType.Real:
case SqlDbType.Float:
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.DateTimeOffset:
case SqlDbType.UniqueIdentifier:
case SqlDbType.Image:
sbCommandText.Append(param.SqlDbType.ToString().ToUpper());
// Unknown
case SqlDbType.Timestamp:
sbCommandText.Append("/* UNKNOWN DATATYPE: ");
sbCommandText.Append(param.SqlDbType.ToString().ToUpper());
sbCommandText.Append(" *" + "/ ");
sbCommandText.Append(param.SqlDbType.ToString().ToUpper());
If you're using SQL Server, you could use SQL Server Profiler (if you have it) to view the command string that is actually executed. That would be useful for copy/paste testing purpuses but not for logging I'm afraid.
4,30321939
I also had this issue where some parameterized queries or sp's would give me a SqlException (mostly the string or binary data would be truncated), and the statements where hard to debug (As far as i know there currently is no sql-profiler support for SQL Azure)
I see a lot of simular code in reactions here. I ended up putting my solution in a Sql-Library project for future use.
The generator is available here:
It supports both CommandType.Text and CommandType.StoredProcedure
And if you install the
you can generate it with this statement:
SqlDebugHelper.CreateExecutableSqlStatement(sql, parameters);
Profiler is hands-down your best option.
You might need to copy a set of statements from profiler due to the prepare + execute steps involved.
24.9k1377125
Used part of Flapper's code for my solution, which returns the entire SQL string including parameter values to run in MS SQL SMS.
public string ParameterValueForSQL(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:
if (sp.Value == DBNull.Value)
retval = "NULL";
retval = "'" + sp.Value.ToString().Replace("'", "''") + "'";
case SqlDbType.Bit:
if (sp.Value == DBNull.Value)
retval = "NULL";
retval = ((bool)sp.Value == false) ? "0" : "1";
if (sp.Value == DBNull.Value)
retval = "NULL";
retval = sp.Value.ToString().Replace("'", "''");
public string CommandAsSql(SqlCommand sc)
string sql = sc.CommandT
sql = sql.Replace("\r\n", "").Replace("\r", "").Replace("\n", "");
sql = System.Text.RegularExpressions.Regex.Replace(sql, @"\s+", " ");
foreach (SqlParameter sp in sc.Parameters)
string spName = sp.ParameterN
string spValue = ParameterValueForSQL(sp);
sql = sql.Replace(spName, spValue);
sql = sql.Replace("= NULL", "IS NULL");
sql = sql.Replace("!= NULL", "IS NOT NULL");
This is what I use to output parameter lists for a stored procedure into the debug console:
string query = (from SqlParameter p in sqlCmd.Parameters where p != null where p.Value != null select string.Format("Param: {0} = {1},
", p.ParameterName, p.Value.ToString())).mandText, (current, parameter) =& current + parameter);
Debug.WriteLine(query);
This will generate a console outputt simlar to this:
Customer.prGetCustomerDetails: @Offset = 1,
Param: @Fetch = 10,
Param: @CategoryLevel1ID = 3,
Param: @VehicleLineID = 9,
Param: @SalesCode1 = bce,
I place this code directly below any procedure I wish to debug and is similar to a sql profiler session but in C#.
This solution works for me right now. Maybe it is usefull to someone. Please excuse all the redundancy.
Public Shared Function SqlString(ByVal cmd As SqlCommand) As String
Dim sbRetVal As New System.Text.StringBuilder()
For Each item As SqlParameter In cmd.Parameters
Select Case item.DbType
Case DbType.String
sbRetVal.AppendFormat("DECLARE {0} AS VARCHAR(255)", item.ParameterName)
sbRetVal.AppendLine()
sbRetVal.AppendFormat("SET {0} = '{1}'", item.ParameterName, item.Value)
sbRetVal.AppendLine()
Case DbType.DateTime
sbRetVal.AppendFormat("DECLARE {0} AS DATETIME", item.ParameterName)
sbRetVal.AppendLine()
sbRetVal.AppendFormat("SET {0} = '{1}'", item.ParameterName, item.Value)
sbRetVal.AppendLine()
Case DbType.Guid
sbRetVal.AppendFormat("DECLARE {0} AS UNIQUEIDENTIFIER", item.ParameterName)
sbRetVal.AppendLine()
sbRetVal.AppendFormat("SET {0} = '{1}'", item.ParameterName, item.Value)
sbRetVal.AppendLine()
Case DbType.Int32
sbRetVal.AppendFormat("DECLARE {0} AS int", item.ParameterName)
sbRetVal.AppendLine()
sbRetVal.AppendFormat("SET {0} = {1}", item.ParameterName, item.Value)
sbRetVal.AppendLine()
End Select
sbRetVal.AppendLine("")
sbRetVal.mandText)
Return sbRetVal.ToString()
End Function
2,07421429
If it's only to check how a parameter is formatted in the result query, most DBMS's will allow querying literals from nothing. Thus:
Using cmd As SqlCommand = Connection.CreateCommand
mandText = "SELECT @Value"
cmd.Parameters.AddWithValue("@Value", "myValue")
Return cmd.ExecuteScalar
That way you can see if quotes are doubled, etc.
9,61695990
I had the same exact question and after reading these responses mistakenly decided it wasn't possible to get the exact resulting query.
I was wrong.
Open Activity Monitor in SQL Server Management Studio, narrow the processes section to the login username, database or application name that your application is using in the connection string.
When the call is made to the db refresh Activity Monitor.
When you see the process, right click on it and View Details.
Note, this may not be a viable option for a busy db.
But you should be able to narrow the result considerably using these steps.
2,72831748
Modified version of Kon's answer as it only partially works with similar named parameters.
The down side of using String Replace function.
Other than that, I give him full credit on the solution.
private string GetActualQuery(SqlCommand sqlcmd)
string query = mandT
string parameters = "";
string[] strArray = System.Text.RegularExpressions.Regex.Split(query, " VALUES ");
//Reconstructs the second half of the SQL Command
parameters = "(";
int count = 0;
foreach (SqlParameter p in sqlcmd.Parameters)
if (count == (sqlcmd.Parameters.Count - 1))
parameters += p.Value.ToString();
parameters += p.Value.ToString() + ", ";
parameters += ")";
//Returns the string recombined.
return strArray[0] + " VALUES " +
As @pkExec and @Alok mentioned, use Replace does not work in 100% of cases.
This is the solution I've used in our DAL that uses RegExp to "match whole word" only and format the datatypes correctly. Thus the SQL generated can be tested directly in MySQL Workbench (or SQLSMS, etc ...) :)
(Replace the MySQLHelper.EscapeString() function according to the DBMS used.)
Dim query As String = mandText
query = query.Replace("SET", "SET" & vbNewLine)
query = query.Replace("WHERE", vbNewLine & "WHERE")
query = query.Replace("GROUP BY", vbNewLine & "GROUP BY")
query = query.Replace("ORDER BY", vbNewLine & "ORDER BY")
query = query.Replace("INNER JOIN", vbNewLine & "INNER JOIN")
query = query.Replace("LEFT JOIN", vbNewLine & "LEFT JOIN")
query = query.Replace("RIGHT JOIN", vbNewLine & "RIGHT JOIN")
If query.Contains("UNION ALL") Then
query = query.Replace("UNION ALL", vbNewLine & "UNION ALL" & vbNewLine)
ElseIf query.Contains("UNION DISTINCT") Then
query = query.Replace("UNION DISTINCT", vbNewLine & "UNION DISTINCT" & vbNewLine)
query = query.Replace("UNION", vbNewLine & "UNION" & vbNewLine)
For Each par In cmd.Parameters
If par.Value Is Nothing OrElse IsDBNull(par.Value) Then
query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", "NULL")
ElseIf TypeOf par.Value Is Date Then
query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", "'" & Format(par.Value, "yyyy-MM-dd HH:mm:ss") & "'")
ElseIf TypeOf par.Value Is TimeSpan Then
query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", "'" & par.Value.ToString & "'")
ElseIf TypeOf par.Value Is Double Or TypeOf par.Value Is Decimal Or TypeOf par.Value Is Single Then
query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", Replace(par.Value.ToString, ",", "."))
ElseIf TypeOf par.Value Is Integer Or TypeOf par.Value Is UInteger Or TypeOf par.Value Is Long Or TypeOf par.Value Is ULong Then
query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", par.Value.ToString)
query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", "'" & MySqlHelper.EscapeString(CStr(par.Value)) & "'")
SELECT * FROM order WHERE order_status = @order_status AND order_date = @order_date
Will be generated:
SELECT * FROM order WHERE order_status = 'C' AND order_date = ' 00:00:00'
Your Answer
Sign up or
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Post as a guest
By posting your answer, you agree to the
Not the answer you're looking for?
Browse other questions tagged
Stack Overflow works best with JavaScript enabled