Do you always write parameterized query?

As you know SQL Server creates an execution plan for the query/stored procedure and stores it in procedure cache in order to reuse it when you run the query next time.
But more and more execution plans in memory will also hurt the performance as we do not have a control the procedure cache's size so what would you do? Lets see the below scenario I've ran on the DEV(DO NOT run on Production) machine. First of all I used DBCC FREEPROCCACHE which clears the procedure cache and causes ad hoc queries to
be recompiled. Next I ran actually the same query with different parameters.

DBCC FREEPROCCACHE

SELECT * FROM Production.WorkOrder
WHERE ProductID=522

SELECT * FROM Production.WorkOrder
WHERE ProductID=737

DECLARE @i INT
set @i = 518
SELECT *
FROM Production.WorkOrder
WHERE ProductID = @i
GO
--See how many plans SQL Server created
SELECT stats.execution_count AS exec_count,
p.size_in_bytes as [size],
[sql].[text] as [plan_text]
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle

/*
1 49152 DECLARE @i INT set @i = 518 SELECT * FROM Production.WorkOrder WHERE ProductID = @i
1 24576 SELECT * FROM Production.WorkOrder WHERE ProductID=737
1 24576 SELECT * FROM Production.WorkOrder WHERE ProductID=522
*/
As you see SQL Server created THREE execution plans.
Now let's wrap the query within a stored procedure and see what will be happened.

CREATE PROCEDURE spTest
@i INT
AS
SELECT *
FROM Production.WorkOrder
WHERE ProductID = @i
GO

EXEC spTest 522
EXEC spTest 737
EXEC spTest 518

--See how many plans SQL Server created
SELECT stats.execution_count AS exec_count,
p.size_in_bytes as [size],
[sql].[text] as [plan_text]
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle

/*
3 40960 CREATE PROCEDURE spTest @i INT AS SELECT * FROM Production.WorkOrder WHERE ProductID = @i
*/
Wow, you we get single execution plan that SQL Server used three times.
I also recommend you to read Tony's blog about the subject
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/07/07/procedure-cache-tuning-sizing-from-1gbyte-to-768kbytes-increase-the-size-of-usable-data-cache.aspx

Tony writes in his blog and I'm completely agree with him.
"Surprised? It's one of the reasons DBA's keep bleating on about using stored procedures, it forces the mindset to use the procedure cache more effectively. By parameterising, but preferably using stored procedures we get plan reuse which means a) less compiles thereby reducing CPU load, b) more available pages for the data cache thereby reducing physical disk IO and c) DBA’s are always right ;)."


PS.Besides using stored procedures we can use sp_executesql with parameters as below

EXEC sp_executesql N'SELECT SUM(ProductID) AS ProductTotal
FROM Production.WorkOrder
WHERE ProductID= @ProductID', N'@ProductID INT', 722

EXEC sp_executesql N'SELECT SUM(ProductID) AS ProductTotal
FROM Production.WorkOrder
WHERE ProductID= @ProductID', N'@ProductID INT', 522

.....

GO