CODEWITHSUNDEEP
sql-serverado.net
DanP
DanP

Reputation: 6478

Unused sql parameters - are they of any harm?

Consider the following code:

Dim sql = "SELECT * FROM MyTable WHERE value1 = @Param1"

If someCondition Then
   sql = sql + " AND value2 = @Param2"
End If

Dim cmd As New SqlCommand(sql, conn)
cmd.Parameters.AddWithValue("@Param1", param1Value)
cmd.Parameters.AddWithValue("@Param2", param2Value)

Assuming that I built a complex sql statement dynamically that may or may not have included the @Param2 parameter - is there any harm in adding it to the command as a parameter?

My real use-case is obviously far more complicated than this, but in general, is this a pattern I should avoid; and if so, why?

Upvotes: 8

Views: 1967

Answers (2)

marc_s
marc_s

Reputation: 754973

The only point I would take note is the fact that if you call .AddWithValue, you leave it up to SQL Server to figure out what the data type of the parameter will be.

SQL Server does a remarkably good job of guessing - but sometimes, it gets it "sub-optimally" and it would be helpful for you to provide the details.

So I personally tend to always use this snippet of code:

SqlParameter aParam = new SqlParameter("@Param1", SqlDbType.VarChar, 50);
aParam.Value = param1Value;

This has two main benefits:

  • you get to define the explicit type, which is important e.g. when using VARCHAR vs. NVARCHAR (otherwise you might incur lots of unnecessary type conversions)
  • you get to define the max length of e.g. string parameters

You could easily wrap this in a e.g. static helper class, or even use it as an extension method.

It's a tad more work, but you get more control, and might avoid unnecessary, time-consuming datatype conversions and other unexpected side effects if you leave it up to SQL Server to guess your types.

Upvotes: 1

hav2play21
hav2play21

Reputation: 171

It is always a best practice to avoid passing in parameters that are not needed. If you pass in a parameter that does not have a value then it is either going to assume that you are looking for a NULL, empty string, or it will give you an error that it cannot process the request.

Upvotes: 0

Related Questions