CODEWITHSUNDEEP
c#sqlstored-proceduresparameterssqlcommand
ChandlerPelhams
ChandlerPelhams

Reputation: 1678

Pass a parameter to SQL server SP with quotes

I am executing a stored procedure on a SQL server using a SqlCommand class from C#. Currently I just build an execution string that parses in the parameter values to the stored procedure then executes the string on the server. The problem is when I have quotes the string does not get passed properly

Is it possible to use SqlParameter objects to pass in the parameters without worrying about escaping out of quotes?

Upvotes: 1

Views: 1519

Answers (3)

Guffa
Guffa

Reputation: 700910

Yes, that is the preferred way of sending parameters.

Example:

using (SqlCommand cmd = new SqlCommand("SomeProcedure", connection) {
  cmd.Parameters.Add("@Id", SqlDbType.Int).Value = 42;
  cmd.Parameters.Add("@Text", SqlDbType.Varchar, 50).Value = "A string value";
  cmd.ExecuteNonQuery();
}

Upvotes: 1

MatBailie
MatBailie

Reputation: 86808

When using SqlParameter you are able to specify the type and size of the parameters. Doing so ensures execution plan re-use (though that's not such an issue with StoredProcs) but also ensure that you do not need to worry about escaping and quoting - it's all done for you.

Upvotes: 0

tomfanning
tomfanning

Reputation: 9670

Simply put, yes - and you should be using SqlParameter objects rather than string concatenation anyway, as a minimum to guard against SQL injection attacks.

Upvotes: 1

Related Questions