CODEWITHSUNDEEP
sqlsql-serversql-server-2005
mameesh
mameesh

Reputation: 3761

Best practice between these two queries

I was in a user group meeting yesterday and they pointed out that using parameterized queries is better than harcoding the query. That got me to thinking, does this do anything beneficial(obviously on a much bigger scale than this though):

DECLARE @Client1 UNIQUEIDENTIFIER,
@Client2 UNIQUEIDENTIFIER
SET @ClientId1 ='41234532-2342-3456-3456-123434543212';
SET @ClientId2 = '12323454-3432-3234-5334-265456787654';

SELECT ClientName
FROM dbo.tblclient
WHERE id IN (@Client1,@Client2)

As opposed to:

SELECT ClientName
FROM dbo.tblclient
WHERE id IN ('41234532-2342-3456-3456-123434543212','12323454-3432-3234-5334-265456787654')

Upvotes: 10

Views: 285

Answers (3)

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

Parametrized queries and IN clause are actually not trivially implemented together if your IN list changes from time to time.

Read this SO question and answers: Parameterize an SQL IN clause

Parameters, by design, are one value only. Everything else other than that must be manually implemented, having in mind security issues, such as SQL Injection.

From a performance perspective, you will have better performance for parametrized queries, specially if a same query is ran repeatedly, but with different parameters values. However, if you have a dynamic IN list (sometimes 2 items, sometimes 3), you might not get the advantage of using parametrized queries.

Do not lose hope, though. Some folks have been able to implement that (Parametrized queries and IN clause). It's, again, not trivial, though.

Upvotes: 4

rownage
rownage

Reputation: 2414

It shouldn't hurt, but you're going to get the most effect from prepared statements when you use queries that are generated by user input. If they're clicking a button to "show all", it's not a big deal; however, if you're prompting for a user to enter their name, you seriously need to parameterize the input before inserting/updating/selecting/etc.

For example, if I entered my name as "Mike DROP TABLE MASTER);" or whatever a big table name is in your DB, It could get really ugly for you. Better safe than sorry, right?

EDIT: OP commented here and asked a question. Updated with a code example.

public int myNum; 
SqlParameter spNum=new SqlParameter("@myNum", SqlDbType.Int); 
//you can also check for null here (but not really relevant in this case)
command.Parameters.Add(spNum); 
string sql="INSERT INTO Table(myNum)";
sql+=" VALUES(@myNum)";
command.CommandText = sql;
int resultsCt = command.ExecuteNonQuery();

See how the code is forcing the input to be an integer BEFORE it does any work with the database? That way if anybody tries any shenanigans it's rejected before it can do harm to the DB.

Upvotes: 1

Martin
Martin

Reputation: 1536

On huge databases and complex queries with many joins the database can use time building an execute plan. When using parameterized queries the execute plan stays in the database cache for some time when calling the query multiple times with different parameters

Upvotes: 1

Related Questions