Best practice between these two queries

Question

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')

Answer 1

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.

Answer 2

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