AddWithValue with Parameterized SQL Gotcha

With Visual Studio 2005, the SQLParameterCollection got a new method, AddWithValue. This method is very handy when executing stored procedures. You need to be careful about using this with dynamic SQL and parameterized SELECT statements. You could end up with very poor performance on your dynamic SQL queries.

Suppose you have a flexible inquiry form that is generating dynamic SQL to find order lines that match an optional set of critera that the user has filled in on a form. The parameters are used to build a custom WHERE clause based on the criteria the user selected.

If the criteria in the database are dates or numbers, you may end up with a very inefficient query when you use the AddWithValue method to create the parameters. By default, the generated SQL will set these parameters to an nvarchar data type. When the SQL runs, the server is expected to convert the nvarchar parameter to the other data type. For numbers and dates, this will prevent the use of an index seek.

Instead, use the standard Add method, which allows you to define the data type of the parameter. Here’s a VB example:

cmd.Parameters.Add(“@OrderQuantity”, SqlDbType.Decimal).Value = myValue

Advertisements
%d bloggers like this: