SQL command parameters Advantages of?

[Mark]

Hi all, a quick ADO.NET question in regards to the command object.
What are the advantages (if any) of specifying command parameters when
executing a stored procedure over just calling the stored procedure via
dynamic SQL?

// Pseudo code below using dynamic SQL
private void InsertPerson(string sName)
{
SqlCommand myCommand = new SqlCommand(........
// Build the dynamic SQL
myCommand.CommandText = "EXEC spAddPerson '" + sName + "'";
// Execute the command against the database
myCommand.ExecuteNonQuery();
}

// Pseudo code below using SQL Parameters
private void InsertPerson(string sName)
{
SqlCommand myCommand = new SqlCommand(........
myCommand.Parameters.Add("@Name", SqlDbType.VarChar);
myCommand.Parameters["@Name"].Value = sName;
myCommand.ExecuteNonQuery();
}

Thanks in advance
Mark

 

[Roy Fine]

Mark,

SQL parameters offer *potentially* many advantages. When presenting the SQL
text to the ADO.NET provider, the text is delimited by the double quote, and
text values within the string are delimited by the single quote. This setup
precludes the use of either of these characters in the value for any fields
in the string - notwithstanding a lot of extra work to escape the sequence
to allow these delimeter character values in as literal characters.

Second - most modern SQL processing engines will parse the SQL statement and
generate an execution plan and cache the two intermediate results - the
parsed form of the SQL statement and the execution plan. If you use bind
variables in the statement, the SQL engine can resue SQL statements that
differ only by the values of fields in the SQL statement. There is a
substantial performance boost in systems with a large number of
transaction - for run-once SQL statements, the advantages are not quite to
apparent.

regards
roy fine

 

[Mark]

Hi Roy, thankyou for your help.
Regards
Mark

Mark,

SQL parameters offer *potentially* many advantages. When presenting the SQL
text to the ADO.NET provider, the text is delimited by the double quote, and
text values within the string are delimited by the single quote. This setup
precludes the use of either of these characters in the value for any fields
in the string - notwithstanding a lot of extra work to escape the sequence
to allow these delimeter character values in as literal characters.

Second - most modern SQL processing engines will parse the SQL statement and
generate an execution plan and cache the two intermediate results - the
parsed form of the SQL statement and the execution plan. If you use bind
variables in the statement, the SQL engine can resue SQL statements that
differ only by the values of fields in the SQL statement. There is a
substantial performance boost in systems with a large number of
transaction - for run-once SQL statements, the advantages are not quite to
apparent.

regards
roy fine

Hi all, a quick ADO.NET question in regards to the command object.
What are the advantages (if any) of specifying command parameters when
executing a stored procedure over just calling the stored procedure via
dynamic SQL?

// Pseudo code below using dynamic SQL
private void InsertPerson(string sName)
{
SqlCommand myCommand = new SqlCommand(........
// Build the dynamic SQL
myCommand.CommandText = "EXEC spAddPerson '" + sName + "'";
// Execute the command against the database
myCommand.ExecuteNonQuery();
}

// Pseudo code below using SQL Parameters
private void InsertPerson(string sName)
{
SqlCommand myCommand = new SqlCommand(........
myCommand.Parameters.Add("@Name", SqlDbType.VarChar);
myCommand.Parameters["@Name"].Value = sName;
myCommand.ExecuteNonQuery();
}

Thanks in advance
Mark