๐ฅ Dynamic SQL
Frequently Asked for Database Developer Roles ๐ฏ
๐ง 1. What is Dynamic SQL?
Dynamic SQL is SQL code that is built and executed at runtime.
๐ Query is created dynamically as a string
๐ Executed later by the database
Unlike normal SQL:
SELECT _ FROM employees
Dynamic SQL:
'SELECT _ FROM employees WHERE department = ''IT'''
โก 2. Why Use Dynamic SQL?
โ Flexible queries
โ Dynamic filtering
โ Dynamic table names
โ Dynamic sorting
Used when query structure changes based on user input.
๐ฅ 3. Dynamic SQL Example (MySQL)
SET @sql_query = 'SELECT * FROM employees';
PREPARE stmt FROM @sql_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
๐ฅ 4. Dynamic Filter Example
SET @dept = 'IT';
SET @sql_query = CONCAT(
'SELECT * FROM employees
WHERE department = ''',
@dept,
''''
);
PREPARE stmt FROM @sql_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
๐ฅ 5. Dynamic Table Name Example
SET @table_name = 'employees';
SET @sql_query = CONCAT('SELECT * FROM ', @table_name);
PREPARE stmt FROM @sql_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
โ ๏ธ 6. SQL Injection Risk
Bad Example โ
SELECT _ FROM users WHERE username = 'user_input'
Can be exploited by attackers.
โ Safe Approach
Use parameterized queries whenever possible.
PREPARE stmt FROM 'SELECT _ FROM employees WHERE department = ?'
๐ฏ 7. Real-World Uses
โ Search filters
โ Reporting systems
โ Dynamic dashboards
โ ETL processes
โ Admin tools
๐ฏ 8. Practice Tasks
1. Create dynamic SELECT query
2. Create dynamic WHERE condition
3. Create dynamic ORDER BY query
4. Query dynamic table name
5. Execute prepared statement
โก Mini Challenge ๐ฅ
๐ Create a dynamic SQL query that:
Accepts a department name
Returns employees from that department
Sorts them by salary DESC
๐ฅ Most asked question: What is the difference between Static SQL and Dynamic SQL?
Aspect: Query
Static SQL: Fixed query
Dynamic SQL: Built at runtime
Aspect: Performance
Static SQL: Faster
Dynamic SQL: More flexible
Aspect: Complexity
Static SQL: Less complex
Dynamic SQL: More complex
Aspect: Risk
Static SQL: Lower risk
Dynamic SQL: Higher risk if not parameterized
Double Tap โค๏ธ For More