SQL Server Performance Forum – Threads Archive

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

Check the execution plan for both the query types and with proper indexes it doesn’t matter much on performancd. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

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.

If you dont pass object name as parameter, why dont you avoind Dynamic sql at all?
www.Sommarskog.se/dynamic_sql.html Madhivanan Failing to plan is Planning to fail

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

]]>