John,

I don't know exacly how you came to that conclusion. Kevin's example *IS* using parameters, which is the reason for bending it into the way he did.

My example does not explicitly use parameters, but can be depending on what is in the whereclause. If the where clause contains parameters, it will be SQL injection free. So it really is up the routine above this layer to provide the right parameters.

As long as the application controls how the SQL is constructed and SQL code injection is blocked in one way or another (you cannot do SQL code injection in textmerging a numeric variable (e.g TOP clause)), I cannot see major problems with it.

There is a less obvious reason for not doing parameters. That is when you need to create a temp table. It is not possible to create a temp table that should persist outside of the transaction with a parameterized SQL statment as that statement is passed to the sp_sqlexecute stored procedure and the temp table will be released as soon as the stored procedure ends.

AS far as the caching argument. That might be an advantage or disadvantage. If the SQL parameters change the selectivity of the query, you might be better off with recompilation of the execution plan rather than reusing it. For the same reason, some of my stored procedures are called with the RECOMPILE option, just to general a fresh execution plan upon each and every call. It could mean the difference between an execution time of 30 seconds and a fraction of a second (I've got such a case).

Walter,

>If a developer uses parameterized queries (which neither of you did for some reason ;-) ) then MS SQL has a significant advantage: cacheing/precompiling of parameterized SQL. Highly recommended in the MS environment for this reason.
>
>Also, MS SQL's support for named parameters rather than placeholder ?s means that its SQL can be a lot easier to read- though others say that the brevity of MySQL is a welcome relief. ;-)
>
>I've already asked Kevin, so I'll ask you too: is there a reason for use of concatenated SQL rather than parameters? I'm assuming it was to provide a quick example? Obviously you wouldn't deploy this in real life because it allows a malicious user to Inject SQL, sending a value that concatenates into SQL that allows access to records that are supposed to be off-limits or closes off your select and starts a new UPDATE statement that damages data. The other way to prevent injection is by extensive parsing/checking of values, but using parameters seems more efficient. JMHO.