Difference between Static and Dynamic SQL

Static SQL and Dynamic SQL are terms used to describe how SQL statements are prepared and executed in a database environment. Let's look at the differences between the two:

Definition:
- Static SQL: The SQL statements are known and defined at compile time. They do not change at runtime.
- Dynamic SQL: The SQL statements are generated and defined at runtime. They can change and be constructed on-the-fly based on input or other runtime conditions.
Flexibility:
- Static SQL: Less flexible because the query is fixed at compile time.
- Dynamic SQL: More flexible as it allows for constructing SQL statements dynamically based on variable values, user inputs, or other runtime factors.
Performance:
- Static SQL: Typically faster, as the database system can optimize the query plan in advance during the compile phase.
- Dynamic SQL: Can have overhead because the SQL statement must be parsed, compiled, and optimized at runtime. However, some databases do cache execution plans for dynamic queries, mitigating this overhead.
Security:
- Static SQL: More secure because it's not susceptible to SQL injection attacks as the query structure is predefined.
- Dynamic SQL: Potentially vulnerable to SQL injection attacks if not properly handled. It's essential to use parameterized queries or prepared statements to ensure security.
Use Cases:
- Static SQL: Suitable for situations where the query structure is known in advance and does not change.
- Dynamic SQL: Useful in scenarios where the query structure depends on certain conditions, such as building reports, dynamic search filters, or constructing queries based on user input.
Implementation:
- Static SQL: Implemented using standard SQL statements in embedded SQL or database APIs.
- Dynamic SQL: Often implemented using string concatenation or database-specific APIs and functions that allow for the execution of string-based SQL statements.
Compilation:
- Static SQL: The SQL statements are bound and optimized at compile time.
- Dynamic SQL: The SQL statements are bound and optimized at execution time.

Examples:

Static SQL:

SELECT first_name, last_name FROM employees WHERE department_id = 10;

Dynamic SQL:

EXECUTE IMMEDIATE 'SELECT first_name, last_name FROM employees WHERE department_id = ' || variable_department_id;

When deciding between static and dynamic SQL, it's essential to consider the requirements of the application, the performance implications, and the security risks. While dynamic SQL provides great flexibility, it's crucial to implement it securely to prevent potential vulnerabilities.

Dynamic SQL Execution and Security Considerations:
- Dynamic SQL Execution:
```
EXECUTE IMMEDIATE 'SELECT * FROM Employees WHERE DepartmentID = :dept_id' USING department_id;
```
- Security Considerations:
  - Always use bind variables or parameterized queries to prevent SQL injection.
  - Validate and sanitize user inputs before constructing dynamic queries.

Static vs Dynamic SQL in Stored Procedures:

Static SQL in Stored Procedure:

CREATE PROCEDURE GetEmployeeInfo
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName FROM Employees;
END;

Dynamic SQL in Stored Procedure:

CREATE PROCEDURE GetEmployeeInfoDynamic(@department_id INT)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = 'SELECT EmployeeID, FirstName, LastName FROM Employees WHERE DepartmentID = ' + CAST(@department_id AS NVARCHAR(MAX));
    EXEC sp_executesql @sql;
END;