RSS Feed

Dynamic SQL

Dynamic SQL

Dynamic SQL describes any SQL DML statement assembled dynamically at runtime as a string and then submitted.

Dynamic SQL is very useful for several tasks:
# Multiple possible query criteria can be dynamically assembled into custom FROM, WHERE and ORDERS BY clauses for flexible queries.
# Code can respond to the schema of the database and generate appropriate triggers, CRUD stored procedures and views.
# Dynamic code can auto-generate very consistent stored procedures.

Drawbacks of Dynamic SQL
# Dynamic SQL that includes user entries in WHERE clauses can be open to SQL injection atacks.
# Poorly written dynamic SQL queries often include extra table references and perform poorly.
# T-SQL code that generates T-SQL code can be tricky to debug.

[Via Microsoft SQL Server 2008 Bible By Paul Nielsen, Uttam Parui]

Below are few awesome resources on Dynamic SQL:

1. Below Dynamic SQL link includes: What is Dynamic SQL, Steps used in processing Dynamic SQL etc.

2. Below Dynamic SQL link includes: What is Dynamic SQL?, Dynamic SQL vs. Stored Procedures, When to Use Dynamic SQL?, How we can use Dynamic SQL etc.

3. The Curse and Blessings of Dynamic SQL include: Introducing Dynamic SQL, Dynamic SQL and Stored Procedures, Good Coding Practices and Tips for Dynamic SQL, Common Cases when to (Not) Use Dynamic SQL etc.

4. Building Dynamic SQL In a Stored Procedure includes: sp_executesql Vs EXECUTE Command, Using Like Operator, IN Operator and Order By In Dynamic SQL etc.
Code Project

5. Introduction to Dynamic SQL - Part-1 and Part-2

6. Using Dynamic SQL Statements in Stored Procedures

7. Dynamic SQL & SQL injection

No comments:

Post a Comment