Dynamic SQL

In the previous lesson, you learned about:

text exampleWoHoTech

MERGE Statement
UPSERT Operations
Data Synchronization

Now we move to the final topic of the:

text exampleWoHoTech

Advanced SQL

module.

The topic is:

text exampleWoHoTech

Dynamic SQL

Dynamic SQL is one of the most powerful and advanced SQL concepts used in enterprise applications.

Unlike normal SQL queries:

text exampleWoHoTech

Static SQL

which are written before execution,

Dynamic SQL allows SQL statements to be:

text exampleWoHoTech

Generated
Modified
Executed

at runtime.

This provides enormous flexibility for applications, reporting systems, and administration tools.

What is Dynamic SQL?

Dynamic SQL is SQL code that is generated and executed during runtime rather than being written completely beforehand.

Example:

Static SQL:

sql exampleWoHoTech

SELECT *
FROM Employees;

Query is fixed.

Dynamic SQL:

sql exampleWoHoTech

SELECT *
FROM + TableName;

Table name can change during execution.

Simple Definition

Dynamic SQL is SQL code that is constructed as a string and executed at runtime.

Why is Dynamic SQL Needed?

Suppose a reporting system allows users to choose:

text exampleWoHoTech

Table
Columns
Filters
Sorting

before generating reports.

A fixed query cannot handle every possibility.

Dynamic SQL solves this problem.

Static SQL vs Dynamic SQL

Static SQL

Written before execution.

Example:

sql exampleWoHoTech

SELECT *
FROM Employees;

Always executes the same query.

Dynamic SQL

Built during execution.

Example:

sql exampleWoHoTech

DECLARE @SQL VARCHAR(500);

SET @SQL =
'SELECT * FROM Employees';

EXEC(@SQL);

Comparison Table

Feature	Static SQL	Dynamic SQL
Query Fixed	Yes	No
Runtime Changes	No	Yes
Performance	Better	Slightly Lower
Flexibility	Limited	High
Complexity	Low	Higher

How Dynamic SQL Works

Process:

text exampleWoHoTech

Build Query String
         ↓
Store In Variable
         ↓
Execute Query

Basic Dynamic SQL Syntax

SQL Server:

sql exampleWoHoTech

DECLARE @SQL VARCHAR(500);

SET @SQL =
'SELECT * FROM Employees';

EXEC(@SQL);

Understanding the Syntax

DECLARE

Creates SQL string variable.

SET

Stores query text.

EXEC()

Executes query string.

First Example

sql exampleWoHoTech

DECLARE @SQL VARCHAR(500);

SET @SQL =
'SELECT * FROM Employees';

EXEC(@SQL);

Result:

text exampleWoHoTech

Employee Records Returned

Dynamic WHERE Clause

Requirement:

text exampleWoHoTech

Filter By Department

Example:

sql exampleWoHoTech

DECLARE @Department VARCHAR(50);

SET @Department = 'HR';

DECLARE @SQL VARCHAR(500);

SET @SQL =

'SELECT *
 FROM Employees
 WHERE Department = '''

+ @Department +

'''';

EXEC(@SQL);

Generated Query

sql exampleWoHoTech

SELECT *
FROM Employees
WHERE Department = 'HR';

Dynamic Table Name

Requirement:

text exampleWoHoTech

User Selects Table

Example:

sql exampleWoHoTech

DECLARE @TableName VARCHAR(100);

SET @TableName =
'Employees';

DECLARE @SQL VARCHAR(500);

SET @SQL =

'SELECT *
 FROM '

+ @TableName;

EXEC(@SQL);

Generated Query

sql exampleWoHoTech

SELECT *
FROM Employees;

Dynamic Column Selection

Example:

sql exampleWoHoTech

DECLARE @ColumnName VARCHAR(100);

SET @ColumnName =
'EmployeeName';

DECLARE @SQL VARCHAR(500);

SET @SQL =

'SELECT '

+ @ColumnName +

' FROM Employees';

EXEC(@SQL);

Generated Query

sql exampleWoHoTech

SELECT EmployeeName
FROM Employees;

Dynamic ORDER BY

Example:

sql exampleWoHoTech

DECLARE @SortColumn VARCHAR(100);

SET @SortColumn =
'Salary';

DECLARE @SQL VARCHAR(500);

SET @SQL =

'SELECT *
 FROM Employees
 ORDER BY '

+ @SortColumn;

EXEC(@SQL);

Generated Query

sql exampleWoHoTech

SELECT *
FROM Employees
ORDER BY Salary;

Dynamic INSERT

Example:

sql exampleWoHoTech

DECLARE @SQL VARCHAR(500);

SET @SQL =

'INSERT INTO Employees
 VALUES
 (
    1,
    ''Rahul'',
    50000
 )';

EXEC(@SQL);

Dynamic UPDATE

Example:

sql exampleWoHoTech

DECLARE @SQL VARCHAR(500);

SET @SQL =

'UPDATE Employees
 SET Salary = 60000
 WHERE EmployeeID = 1';

EXEC(@SQL);

Dynamic DELETE

Example:

sql exampleWoHoTech

DECLARE @SQL VARCHAR(500);

SET @SQL =

'DELETE FROM Employees
 WHERE EmployeeID = 1';

EXEC(@SQL);

EXEC vs sp_executesql

SQL Server provides:

text exampleWoHoTech

EXEC()
sp_executesql

EXEC()

Basic execution.

Example:

sql exampleWoHoTech

EXEC(@SQL);

sp_executesql

Advanced execution.

Supports:

text exampleWoHoTech

Parameters
Caching
Security
Performance

Example

sql exampleWoHoTech

DECLARE @SQL NVARCHAR(500);

SET @SQL =

N'SELECT *
  FROM Employees
  WHERE Salary > @MinSalary';

Execution:

sql exampleWoHoTech

EXEC sp_executesql

@SQL,

N'@MinSalary INT',

@MinSalary = 50000;

Why sp_executesql is Better

Benefits:

text exampleWoHoTech

Safer
Faster
Reusable
Parameterized

SQL Injection Risk

Major concern with Dynamic SQL.

Dangerous Example

sql exampleWoHoTech

SET @UserInput =
''' OR 1=1 --';

Generated query:

sql exampleWoHoTech

SELECT *
FROM Users

WHERE Username = ''

OR 1=1;

Result:

text exampleWoHoTech

Security Breach

What is SQL Injection?

SQL Injection occurs when attackers insert malicious SQL code into application inputs.

It is one of the most common database security vulnerabilities.

Prevention Techniques

Use:

Parameterized Queries Input Validation sp_executesql Stored Procedures

Safe Example

sql exampleWoHoTech

EXEC sp_executesql

@SQL,

N'@Username VARCHAR(50)',

@Username = @Input;

Real-World Example: Reporting System

User chooses:

text exampleWoHoTech

Department
Date Range
Sorting

Dynamic SQL builds custom report queries.

Real-World Example: E-Commerce

User selects:

text exampleWoHoTech

Category
Price Range
Brand

Dynamic filtering generated automatically.

Real-World Example: Banking

Requirement:

text exampleWoHoTech

Custom Financial Reports

Dynamic SQL generates reports based on user-selected criteria.

Real-World Example: University System

Requirement:

text exampleWoHoTech

Dynamic Student Reports

Based on:

text exampleWoHoTech

Department
Semester
CGPA

Real-World Example: Payroll

Generate:

text exampleWoHoTech

Monthly Payroll Reports
Department Reports
Tax Reports

using Dynamic SQL.

Advantages of Dynamic SQL

High Flexibility

Query changes at runtime.

Supports Dynamic Reporting

Major use case.

Reduces Duplicate Code

Reusable query generation.

Supports Metadata-Driven Systems

Useful in enterprise applications.

Powerful Administration Tool

Used by DBAs.

Disadvantages of Dynamic SQL

SQL Injection Risk

Most serious concern.

Harder Debugging

Generated queries can be difficult to trace.

Performance Overhead

Parsing occurs at runtime.

Complex Maintenance

Large dynamic queries become difficult to manage.

Common Mistakes

Ignoring SQL Injection

Most dangerous mistake.

String Concatenation Errors

Missing quotes cause failures.

Using EXEC Everywhere

Prefer sp_executesql when possible.

Poor Input Validation

Security vulnerability.

Best Practices

Prefer sp_executesql

More secure and efficient.

Validate User Input

Always sanitize inputs.

Use Parameters

Avoid string concatenation.

Log Generated Queries

Helps debugging.

Limit Dynamic SQL Usage

Use only when necessary.

Dynamic SQL vs Stored Procedures

Feature	Dynamic SQL	Stored Procedure
Runtime Changes	Yes	No
Security	Lower	Higher
Performance	Moderate	Better
Flexibility	High	Limited
Maintenance	Harder	Easier

Common Interview Questions

What is Dynamic SQL?

SQL code generated and executed at runtime.

Why is Dynamic SQL used?

To create flexible queries based on runtime requirements.

What is SQL Injection?

A security attack that injects malicious SQL into queries.

Which is better: EXEC or sp_executesql?

text exampleWoHoTech

sp_executesql

because it supports parameters and improves security.

What is the biggest risk of Dynamic SQL?

text exampleWoHoTech

SQL Injection

Summary

Dynamic SQL allows SQL statements to be generated and executed at runtime, providing flexibility for reporting systems, enterprise applications, administration tools, and data-driven platforms. While powerful, it must be used carefully because improper implementation can lead to security vulnerabilities such as SQL Injection.

In this lesson, you learned:

What Dynamic SQL is
Static SQL vs Dynamic SQL
EXEC()
sp_executesql
Dynamic Filtering
Dynamic Tables
SQL Injection
Security Best Practices
Real-world Examples
Advantages and Disadvantages

Mastering Dynamic SQL is important because enterprise applications often require flexible query generation based on user input and business requirements.

Advanced SQL Module Completed ✅

Completed files:

advanced-sql/ ├── common-table-expressions.mdx ├── recursive-cte.mdx ├── window-functions.mdx ├── rank-dense-rank-row-number.mdx ├── pivot-unpivot.mdx ├── case-expression.mdx ├── merge-statement.mdx └── dynamic-sql.mdx

Next Folder

optimization/ ├── query-optimization.mdx ├── explain-plan.mdx ├── execution-plan.mdx ├── indexing-strategies.mdx ├── partitioning.mdx └── sql-performance-tips.mdx

Next Topic → Query Optimization

In the previous lesson, you learned about:

text exampleWoHoTech

MERGE Statement
UPSERT Operations
Data Synchronization

Now we move to the final topic of the:

text exampleWoHoTech

Advanced SQL

module.

The topic is:

text exampleWoHoTech

Dynamic SQL

Dynamic SQL is one of the most powerful and advanced SQL concepts used in enterprise applications.

Unlike normal SQL queries:

text exampleWoHoTech

Static SQL

which are written before execution,

Dynamic SQL allows SQL statements to be:

text exampleWoHoTech

Generated
Modified
Executed

at runtime.

This provides enormous flexibility for applications, reporting systems, and administration tools.

What is Dynamic SQL?

Dynamic SQL is SQL code that is generated and executed during runtime rather than being written completely beforehand.

Example:

Static SQL:

sql exampleWoHoTech

SELECT *
FROM Employees;

Query is fixed.

Dynamic SQL:

sql exampleWoHoTech

SELECT *
FROM + TableName;

Table name can change during execution.

Simple Definition

Dynamic SQL is SQL code that is constructed as a string and executed at runtime.

Why is Dynamic SQL Needed?

Suppose a reporting system allows users to choose:

text exampleWoHoTech

Table
Columns
Filters
Sorting

before generating reports.

A fixed query cannot handle every possibility.

Dynamic SQL solves this problem.

Static SQL vs Dynamic SQL

Static SQL

Written before execution.

Example:

sql exampleWoHoTech

SELECT *
FROM Employees;

Always executes the same query.

Dynamic SQL

Built during execution.

Example:

sql exampleWoHoTech

DECLARE @SQL VARCHAR(500);

SET @SQL =
'SELECT * FROM Employees';

EXEC(@SQL);

Comparison Table

Feature	Static SQL	Dynamic SQL
Query Fixed	Yes	No
Runtime Changes	No	Yes
Performance	Better	Slightly Lower
Flexibility	Limited	High
Complexity	Low	Higher

How Dynamic SQL Works

Process:

text exampleWoHoTech

Build Query String
         ↓
Store In Variable
         ↓
Execute Query

Basic Dynamic SQL Syntax

SQL Server:

sql exampleWoHoTech

DECLARE @SQL VARCHAR(500);

SET @SQL =
'SELECT * FROM Employees';

EXEC(@SQL);

Understanding the Syntax

DECLARE

Creates SQL string variable.

SET

Stores query text.

EXEC()

Executes query string.

First Example

sql exampleWoHoTech

DECLARE @SQL VARCHAR(500);

SET @SQL =
'SELECT * FROM Employees';

EXEC(@SQL);

Result:

text exampleWoHoTech

Employee Records Returned

Dynamic WHERE Clause

Requirement:

text exampleWoHoTech

Filter By Department

Example:

sql exampleWoHoTech

DECLARE @Department VARCHAR(50);

SET @Department = 'HR';

DECLARE @SQL VARCHAR(500);

SET @SQL =

'SELECT *
 FROM Employees
 WHERE Department = '''

+ @Department +

'''';

EXEC(@SQL);

Generated Query

sql exampleWoHoTech

SELECT *
FROM Employees
WHERE Department = 'HR';

Dynamic Table Name

Requirement:

text exampleWoHoTech

User Selects Table

Example:

sql exampleWoHoTech

DECLARE @TableName VARCHAR(100);

SET @TableName =
'Employees';

DECLARE @SQL VARCHAR(500);

SET @SQL =

'SELECT *
 FROM '

+ @TableName;

EXEC(@SQL);

Generated Query

sql exampleWoHoTech

SELECT *
FROM Employees;

Dynamic Column Selection

Example:

sql exampleWoHoTech

DECLARE @ColumnName VARCHAR(100);

SET @ColumnName =
'EmployeeName';

DECLARE @SQL VARCHAR(500);

SET @SQL =

'SELECT '

+ @ColumnName +

' FROM Employees';

EXEC(@SQL);

Generated Query

sql exampleWoHoTech

SELECT EmployeeName
FROM Employees;

Dynamic ORDER BY

Example:

sql exampleWoHoTech

DECLARE @SortColumn VARCHAR(100);

SET @SortColumn =
'Salary';

DECLARE @SQL VARCHAR(500);

SET @SQL =

'SELECT *
 FROM Employees
 ORDER BY '

+ @SortColumn;

EXEC(@SQL);

Generated Query

sql exampleWoHoTech

SELECT *
FROM Employees
ORDER BY Salary;

Dynamic INSERT

Example:

sql exampleWoHoTech

DECLARE @SQL VARCHAR(500);

SET @SQL =

'INSERT INTO Employees
 VALUES
 (
    1,
    ''Rahul'',
    50000
 )';

EXEC(@SQL);

Dynamic UPDATE

Example:

sql exampleWoHoTech

DECLARE @SQL VARCHAR(500);

SET @SQL =

'UPDATE Employees
 SET Salary = 60000
 WHERE EmployeeID = 1';

EXEC(@SQL);

Dynamic DELETE

Example:

sql exampleWoHoTech

DECLARE @SQL VARCHAR(500);

SET @SQL =

'DELETE FROM Employees
 WHERE EmployeeID = 1';

EXEC(@SQL);

EXEC vs sp_executesql

SQL Server provides:

text exampleWoHoTech

EXEC()
sp_executesql

EXEC()

Basic execution.

Example:

sql exampleWoHoTech

EXEC(@SQL);

sp_executesql

Advanced execution.

Supports:

text exampleWoHoTech

Parameters
Caching
Security
Performance

Example

sql exampleWoHoTech

DECLARE @SQL NVARCHAR(500);

SET @SQL =

N'SELECT *
  FROM Employees
  WHERE Salary > @MinSalary';

Execution:

sql exampleWoHoTech

EXEC sp_executesql

@SQL,

N'@MinSalary INT',

@MinSalary = 50000;

Why sp_executesql is Better

Benefits:

text exampleWoHoTech

Safer
Faster
Reusable
Parameterized

SQL Injection Risk

Major concern with Dynamic SQL.

Dangerous Example

sql exampleWoHoTech

SET @UserInput =
''' OR 1=1 --';

Generated query:

sql exampleWoHoTech

SELECT *
FROM Users

WHERE Username = ''

OR 1=1;

Result:

text exampleWoHoTech

Security Breach

What is SQL Injection?

SQL Injection occurs when attackers insert malicious SQL code into application inputs.

It is one of the most common database security vulnerabilities.

Prevention Techniques

Use:

Parameterized Queries Input Validation sp_executesql Stored Procedures

Safe Example

sql exampleWoHoTech

EXEC sp_executesql

@SQL,

N'@Username VARCHAR(50)',

@Username = @Input;

Real-World Example: Reporting System

User chooses:

text exampleWoHoTech

Department
Date Range
Sorting

Dynamic SQL builds custom report queries.

Real-World Example: E-Commerce

User selects:

text exampleWoHoTech

Category
Price Range
Brand

Dynamic filtering generated automatically.

Real-World Example: Banking

Requirement:

text exampleWoHoTech

Custom Financial Reports

Dynamic SQL generates reports based on user-selected criteria.

Real-World Example: University System

Requirement:

text exampleWoHoTech

Dynamic Student Reports

Based on:

text exampleWoHoTech

Department
Semester
CGPA

Real-World Example: Payroll

Generate:

text exampleWoHoTech

Monthly Payroll Reports
Department Reports
Tax Reports

using Dynamic SQL.

Advantages of Dynamic SQL

High Flexibility

Query changes at runtime.

Supports Dynamic Reporting

Major use case.

Reduces Duplicate Code

Reusable query generation.

Supports Metadata-Driven Systems

Useful in enterprise applications.

Powerful Administration Tool

Used by DBAs.

Disadvantages of Dynamic SQL

SQL Injection Risk

Most serious concern.

Harder Debugging

Generated queries can be difficult to trace.

Performance Overhead

Parsing occurs at runtime.

Complex Maintenance

Large dynamic queries become difficult to manage.

Common Mistakes

Ignoring SQL Injection

Most dangerous mistake.

String Concatenation Errors

Missing quotes cause failures.

Using EXEC Everywhere

Prefer sp_executesql when possible.

Poor Input Validation

Security vulnerability.

Best Practices

Prefer sp_executesql

More secure and efficient.

Validate User Input

Always sanitize inputs.

Use Parameters

Avoid string concatenation.

Log Generated Queries

Helps debugging.

Limit Dynamic SQL Usage

Use only when necessary.

Dynamic SQL vs Stored Procedures

Feature	Dynamic SQL	Stored Procedure
Runtime Changes	Yes	No
Security	Lower	Higher
Performance	Moderate	Better
Flexibility	High	Limited
Maintenance	Harder	Easier

Common Interview Questions

What is Dynamic SQL?

SQL code generated and executed at runtime.

Why is Dynamic SQL used?

To create flexible queries based on runtime requirements.

What is SQL Injection?

A security attack that injects malicious SQL into queries.

Which is better: EXEC or sp_executesql?

text exampleWoHoTech

sp_executesql

because it supports parameters and improves security.

What is the biggest risk of Dynamic SQL?

text exampleWoHoTech

SQL Injection

Summary

In this lesson, you learned:

What Dynamic SQL is
Static SQL vs Dynamic SQL
EXEC()
sp_executesql
Dynamic Filtering
Dynamic Tables
SQL Injection
Security Best Practices
Real-world Examples
Advantages and Disadvantages

Mastering Dynamic SQL is important because enterprise applications often require flexible query generation based on user input and business requirements.

Advanced SQL Module Completed ✅

Completed files:

Next Folder

optimization/ ├── query-optimization.mdx ├── explain-plan.mdx ├── execution-plan.mdx ├── indexing-strategies.mdx ├── partitioning.mdx └── sql-performance-tips.mdx

Next Topic → Query Optimization

What is Dynamic SQL?

Simple Definition

Why is Dynamic SQL Needed?

Static SQL vs Dynamic SQL

Static SQL

Dynamic SQL

Comparison Table

How Dynamic SQL Works

Basic Dynamic SQL Syntax

Understanding the Syntax

DECLARE

SET

EXEC()

First Example

Dynamic WHERE Clause

Generated Query

Dynamic Table Name

Generated Query

Dynamic Column Selection

Generated Query

Dynamic ORDER BY

Generated Query

Dynamic INSERT

Dynamic UPDATE

Dynamic DELETE

EXEC vs sp_executesql

EXEC()

sp_executesql

Example

Why sp_executesql is Better

SQL Injection Risk

Dangerous Example

What is SQL Injection?

Prevention Techniques

Safe Example

Real-World Example: Reporting System

Real-World Example: E-Commerce

Real-World Example: Banking

Real-World Example: University System

Real-World Example: Payroll

Advantages of Dynamic SQL

High Flexibility

Supports Dynamic Reporting

Reduces Duplicate Code

Supports Metadata-Driven Systems

Powerful Administration Tool

Disadvantages of Dynamic SQL

SQL Injection Risk

Harder Debugging

Performance Overhead

Complex Maintenance

Common Mistakes

Ignoring SQL Injection

String Concatenation Errors

Using EXEC Everywhere

Poor Input Validation

Best Practices

Prefer sp_executesql

Validate User Input

Use Parameters

Log Generated Queries

Limit Dynamic SQL Usage

Dynamic SQL vs Stored Procedures

Common Interview Questions

What is Dynamic SQL?

Why is Dynamic SQL used?

What is SQL Injection?

Which is better: EXEC or sp_executesql?

What is the biggest risk of Dynamic SQL?

Summary

Advanced SQL Module Completed ✅

Next Folder

Continue learning this concept

What is Dynamic SQL?

Simple Definition

Why is Dynamic SQL Needed?

Static SQL vs Dynamic SQL

Static SQL

Dynamic SQL

Comparison Table