In my last post I indicated that even static variables passed to SQL statements should be bound using Cfqueryparam. My understanding was that the DB server could only create cached plans if all the variables in the statement were bound - so I believed that a statement like the following:
Using a DB where the activity is completely under your control (like a local installation). count the rows in the sys.dm_exec_cached_plans table (that's where MSSQL stores the cached plans). Then run the query above. Recount and you should see the number increase by 1. Rerun the same query and the number stays exactly the same - so no "new" execution plan was created. Now change active = 1 to active = 2 and rerun the query. Viola - the number increments by 1. A new cached plan has been inserted. You now have a cached plan for "active = 1" and another cached plan for "active = 2".
This means that it is perfectly acceptable to use static variables in your SQL statements. It also means that some queries run without CFQUERYPARAM that use the same data over and over again are likely still benefiting from cached plans. Of course this is the performance side of the argument. CFQUERYPARAM is still important for security and there are many more reasons to use it than not to use it.