>I'm reading this article (thanks Naomi) for building dynamic queries from variable user input. The user might provide one search field or several:
>http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/do-you-use-column-param-or-param-is-null
>
>In the article the author uses a built in stored procedure for executing dynamic sql select statements called sp_executesql
>
>This stored procedure has a weird way of calling it with parameters which I assumed was a way of calling dynamic sql using parameterized variables.
>
>However, on the MS page for this (http://msdn.microsoft.com/en-us/library/ms188001.aspx) the first warning is that "Run time-compiled Transact-SQL statements can expose applications to malicious attacks, such as SQL injection."
>
>Can anyone shed any light here?
>
>My goal is to create a stored procedure that dynamically creates a query based on variable user input, but does so in a parameterized way to avoid any sql injection vulnerabilities.

Take a look at another article also
Avoid Conversions In Execution Plans By Using sp_executesql Instead of Exec

The main point here that if you're using sp_executeSQL and don't embed parameters into dynamic SQL as strings, you can still re-use the execution plans and the queries are not prone to SQL injection attacks.

Only when you try to embed the parameters inside the SQL, the injection attacks are playing a huge role.