The Curse and Blessings of Dynamic SQL
I came across one good article on Dynamic SQL (SQL Server) by Erland Sommarskog,
SQL Server MVP.
The Article describes Intro to Dynamic SQL, SQL Injection, Dynamic Queries and SPs, Good Coding Practice and Tips for Dynamic SQL, and the common cases where not to use Dynamic SQL:
In this article I will discuss the use of dynamic SQL
in stored procedures and to a minor extent from client languages. To set the
scene, I start with a very quick overview on application
architecture for data access. I then proceed to describe the feature dynamic
SQL as such,
with a quick introduction followed by the gory syntax details. Next, I continue with a discussion on SQL injection, a
security issue that you absolutely must have good understanding of when
you work with dynamic SQL. This is followed by a section where I discuss why
we use stored procedures, and how that is affected by the use of dynamic SQL.
I carryon with a section on good practices and tips for writing
dynamic SQL. I conclude by reviewing a number of
situations where you could use dynamic SQL and
whether it is a good or bad idea to do it. Read More...