Ad Hoc queries vs Command Parameters

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

Hi, is it better or more efficient or somehow "different" to make an ad hoc
query using the Parameters collection of a Command object, than constructing
a sql string for the CommandText property?
Example:

//assumes a LastName parameter
str Cmd = "SELECT FirstName FROM Employees WHERE (LastName = @LastName)"

//vs

//assumes a aString with a value
str Cmd = "SELECT FirstName FROM Employees WHERE LastName = ' " +aString+
" ' " ;
 
Hi Alex,

When you use the command properties there are in my opinion two advantages.

1. it is better readable in a program.
2. it is easier to convert to a stored procedure.

However when it is ad hoc I would take the one which you are most used to
and not think to long about this problem.

Just my thought,

Cor
 
Hi, is it better or more efficient or somehow "different" to make an ad
hoc query using the Parameters collection of a Command object, than
constructing a sql string for the CommandText property?
Example:

//assumes a LastName parameter
str Cmd = "SELECT FirstName FROM Employees WHERE (LastName =
@LastName)"

//vs

//assumes a aString with a value
str Cmd = "SELECT FirstName FROM Employees WHERE LastName = ' "
+aString+ " ' " ;
Click to expand...

SqlServer (and others) will cache the execution plan of the
parameterized query. Which means that the next time you execute the same
query, it will be very fast, as the query is already 'in cache', which
means that the execution plan how to execute teh query is already
available.

The non-parametrized query is not cached, unless sqlserver can
parameterize the query but gives up very easily. This means that each
query is compiled every time it is executed.

Not only that, but parameterized queries do not have the Sql
injection attack issue which non-parameterized queries have.

FB
 
Now, those are very good reasons to use Parameters, in addition to Cor
reasons. Thanks a lot!

alex.
 
Also you minimize if not remove the ability for hackers to use SQL Injection on
your site.

Will
Now, those are very good reasons to use Parameters, in addition to Cor
reasons. Thanks a lot!

alex.
Click to expand...
 
And the top argument in my mind is that it helps to avoid problems with data
expressed in plain text such as doubling quotes for strings, handling
decimal separator ("," for french developers) and various date formats...

Patrice

William said:
Also you minimize if not remove the ability for hackers to use SQL Injection on
your site.

Will
Click to expand...
 
Back
Top