Parameterized query vs local variable in a query

Not exectly sure what the heck the parameterized query really means. Does it always use sp_executesql? like:

DECLARE @UserName NVARCHAR(255)
SET @UserName = suser_sname()
EXEC sp_executesql N'SELECT * FROM UserLog WHERE UserName = @n',
N'@n nvarchar(255)', @UserName
GO

and so this like using a sproc and is different than just using local variable in a query, like:

DECLARE @StartOrderDate datetime
SET @StartOrderDate = '20051131'
SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderId AND h.OrderDate >=
@StartOrderDate

You can use sp_ExecuteSQL with parameters if you do lots of dynamic SQL, to improve SQL Server's chances of finding an execution plan hat it can reuse.

You can call static queries the same way, but AFAIK there's no additional benefit.

One more additional point would be for Both Adhoc and parameterized queries thay are space sensitive and case sensitive...

So

select * from <table> where <col> = 100 is different from

select * from <table> where <col> = 100

And thus plan will not be used..

Please refer this good documentation on this:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

So my question was how parameterized queries are different from just a parameter passed in a local variable....

Is parameterized querie always supposed to use dynamic sql, be it sp_executesql or exec (@var). Basically the definition of parameterized queries.

read this article
http://www.sommarskog.se/dynamic_sql.html

Bikeboy,

There is no formal definition of "parameterized query". You can use variables to pass values, like in a stored procedure, or you can use them to compile a dynamic query statement, for instance to use different columns.

You can use sp_ExecuteSQL with parameters to pass filter values, regardless of whether the query statement itself is dynamic or static. (By the way, if the satement is static then you might consider making it a stored procedure.)