Difference between Static SQL and Dynamic SQL

Static SQL and Dynamic SQL are two approaches to writing SQL statements in applications. Static SQL uses fixed, hard-coded queries known at compile time, while Dynamic SQL constructs queries at runtime based on user input or program logic.

Static SQL

Static SQL refers to SQL statements that are fixed and hard-coded into the application. Since the queries are known at compile time, they can be pre-analyzed, optimized, and do not require special security handling.

Example

-- Static SQL: query is fixed at compile time
SELECT name, salary
FROM employees
WHERE department = 'Engineering';

Dynamic SQL

Dynamic SQL refers to SQL statements that are generated at runtime based on user input or application logic. Dynamic SQL provides flexibility for building general-purpose applications, but requires extra care for security (to prevent SQL injection) and permissions handling.

Example

-- Dynamic SQL: query built at runtime
DECLARE @tableName VARCHAR(50) = 'employees';
DECLARE @sql NVARCHAR(MAX);

SET @sql = 'SELECT * FROM ' + @tableName;
EXECUTE sp_executesql @sql;

Key Differences

Conclusion

Static SQL is faster and safer since queries are fixed and optimized at compile time. Dynamic SQL offers greater flexibility for building adaptive applications but requires careful handling to prevent SQL injection. Use parameterized queries with Dynamic SQL to maintain both flexibility and security.