Explicit Parameters for Stored Procedures

[Mark]

I created a test to check the execution time difference between executing a
SQL Server stored procedured using explicit parameters versus not. In one
case I created new SqlParameters in the code, and added the parameters to
the SqlParametersCollection of the SqlCommand object. In the second, I just
made it all into long execution string. I found that both executed with
the same speed. I would claim that the second (shorter) method is easier to
maintain AND quicker to write. Why wouldn't we always use the second
method?

If you're interested, I've included the code same below that illustrates my
test. Thanks in advance.

Mark

******

SqlConnection sqlConn = new SqlConnection(strConn);
SqlCommand sqlComm = new SqlCommand("p_test", sqlConn);

// Mark the Command as a SPROC
sqlComm.CommandType = CommandType.StoredProcedure;

sqlComm.Parameters.Add(new SqlParameter("@param1", SqlDbType.VarChar, 30));
sqlComm.Parameters.Add(new SqlParameter("@param2", SqlDbType.VarChar, 50));
sqlComm.Parameters.Add(new SqlParameter("@param3", SqlDbType.VarChar, 30));
sqlComm.Parameters.Add(new SqlParameter("@param4", SqlDbType.VarChar, 10));
sqlComm.Parameters.Add(new SqlParameter("@param5", SqlDbType.VarChar, 255));
sqlComm.Parameters.Add(new SqlParameter("@param6", SqlDbType.VarChar, 255));
sqlComm.Parameters.Add(new SqlParameter("@param7", SqlDbType.VarChar, 50));

//Test fields
sqlComm.Parameters.Add(new SqlParameter("@param8", SqlDbType.Char, 1));
sqlComm.Parameters.Add(new SqlParameter("@param9", SqlDbType.DateTime));

sqlComm.Parameters["@param1"].Value = "whatever";
sqlComm.Parameters["@param2"].Value = "whatever";
sqlComm.Parameters["@param3"].Value = "whatever";
sqlComm.Parameters["@param4"].Value = "whatever";
sqlComm.Parameters["@param5"].Value = "whatever";
sqlComm.Parameters["@param6"].Value = "whatever";
sqlComm.Parameters["@param7"].Value = "whatever";

//Test params
sqlComm.Parameters["@param8"].Value = "Y";

for (Int32 i = 0; i < 10; i++)
{
//Execute the command
sqlComm.Parameters["@param9"].Value = DateTime.Now;
sqlConn.Open();
sqlComm.ExecuteNonQuery();
sqlConn.Close();
}

// ***** NOW TEST THE SECOND (QUICKER) METHOD *****

sqlComm.CommandType = CommandType.Text;
String strExecute = "exec p_test 'whatever', 'whatever', 'whatever',
'whatever', 'whatever', 'whatever', 'whatever','N'";

for (Int32 j = 0; j < 10; j++)
{
//Execute the command
sqlComm.CommandText = strExecute + ", '" + DateTime.Now.ToString() + "'";
sqlConn.Open();
sqlComm.ExecuteNonQuery();
sqlConn.Close();
}

 

[David Browne]

I created a test to check the execution time difference between executing a
SQL Server stored procedured using explicit parameters versus not. In one
case I created new SqlParameters in the code, and added the parameters to
the SqlParametersCollection of the SqlCommand object. In the second, I just
made it all into long execution string. I found that both executed with
the same speed. I would claim that the second (shorter) method is easier to
maintain AND quicker to write.
Why wouldn't we always use the second
method?

But you are in charge of converting all of the parameters to strings, which
can be a source of errors. And (turn on the profiler) you will be using SQL
Batches instead of RPC's to invoke the stored procedure. You probably won't
notice the difference running just one at a time, but the RPC mechanism is
more efficient on the network.


Moreover, the string method is not really any easier or shorter. Look at
the Data Access Application block for how to call a stored procedure with
bound parameters in a single, compact line of code.



David

 

[William Ryan]

Depending on how you are implementing the second, you may not be taking
advantage of cached execution plans. moreoever, you can't really conclude
from one test that they run the same speed and even if they do, that
everything always will. In addition, just let someone pass in the name
O'Reilly or anything with an apostrophe just once. The time it takes you to
acknwoledge the error then find it and fix it will be more time than you'll
ever save by using abbreviated syntax without params. I've heard sooo many
people claim that they'd never forget to check for an apostrophe, and
invariably they do. Then there's the issue of passing really long strings
which invariably occur over the network.

IMHO, the little bit of syntax saved is scarcely worth all of the headaches
that come with it.

HTH,

Bill

I created a test to check the execution time difference between executing a
SQL Server stored procedured using explicit parameters versus not. In one
case I created new SqlParameters in the code, and added the parameters to
the SqlParametersCollection of the SqlCommand object. In the second, I just
made it all into long execution string. I found that both executed with
the same speed. I would claim that the second (shorter) method is easier to
maintain AND quicker to write. Why wouldn't we always use the second
method?

If you're interested, I've included the code same below that illustrates my
test. Thanks in advance.

Mark

******

SqlConnection sqlConn = new SqlConnection(strConn);
SqlCommand sqlComm = new SqlCommand("p_test", sqlConn);

// Mark the Command as a SPROC
sqlComm.CommandType = CommandType.StoredProcedure;

sqlComm.Parameters.Add(new SqlParameter("@param1", SqlDbType.VarChar, 30));
sqlComm.Parameters.Add(new SqlParameter("@param2", SqlDbType.VarChar, 50));
sqlComm.Parameters.Add(new SqlParameter("@param3", SqlDbType.VarChar, 30));
sqlComm.Parameters.Add(new SqlParameter("@param4", SqlDbType.VarChar, 10));
sqlComm.Parameters.Add(new SqlParameter("@param5", SqlDbType.VarChar, 255));
sqlComm.Parameters.Add(new SqlParameter("@param6", SqlDbType.VarChar, 255));
sqlComm.Parameters.Add(new SqlParameter("@param7", SqlDbType.VarChar, 50));

//Test fields
sqlComm.Parameters.Add(new SqlParameter("@param8", SqlDbType.Char, 1));
sqlComm.Parameters.Add(new SqlParameter("@param9", SqlDbType.DateTime));

sqlComm.Parameters["@param1"].Value = "whatever";
sqlComm.Parameters["@param2"].Value = "whatever";
sqlComm.Parameters["@param3"].Value = "whatever";
sqlComm.Parameters["@param4"].Value = "whatever";
sqlComm.Parameters["@param5"].Value = "whatever";
sqlComm.Parameters["@param6"].Value = "whatever";
sqlComm.Parameters["@param7"].Value = "whatever";

//Test params
sqlComm.Parameters["@param8"].Value = "Y";

for (Int32 i = 0; i < 10; i++)
{
//Execute the command
sqlComm.Parameters["@param9"].Value = DateTime.Now;
sqlConn.Open();
sqlComm.ExecuteNonQuery();
sqlConn.Close();
}

// ***** NOW TEST THE SECOND (QUICKER) METHOD *****

sqlComm.CommandType = CommandType.Text;
String strExecute = "exec p_test 'whatever', 'whatever', 'whatever',
'whatever', 'whatever', 'whatever', 'whatever','N'";

for (Int32 j = 0; j < 10; j++)
{
//Execute the command
sqlComm.CommandText = strExecute + ", '" + DateTime.Now.ToString() + "'";
sqlConn.Open();
sqlComm.ExecuteNonQuery();
sqlConn.Close();
}