Lately I have been looking for new ways to improve the performance/scalability of my t-sql code. I had remembered that with ORACLE and using cfqueryparam, took advantage of ORACLE's bind variables functionality.
Basically a bind variable, means it treats the queries the same, in terms of performance/explanation plans, even though certain variables are different.
Now while cfqueryparam is always a good practice for preventing sql injection and protecting, I was not aware of how to do bind variables for SQL Server, until now.
is a very powerful but limited dynamic sql building tool.
N'select * from pubs.dbo.employee where job_lvl = @level',
@level = 35
Above is an example from http://doc.ddart.net/mssql/sql70/sp_ea-ez_4.htm
Notice how N is in front of each line, that is to force it to be nvarchar, because the stored procedure accepts only nvarchar, even though the fields in your table may just be int, varchar etc.
Secondly this allows 3 parameters
first parameter is the sql query
second parameter is the declaration of any incoming parameters
third parameter is the setting of those parameters
The problem is that if you have multiple incoming parameters, they all have to be declared on one line. The Sql query can be multiple lines but itself consists of the first slot in this stored procedure.
So if you want to not use up memory by caching a query, this is one way to improve performance, by taking advantage of the bind variables, capability.
BTW, if you want more tips on t-sql query optimization, I recommend that you check out this article.