SQL 动态 SQL 在存储过程中的应用技巧

动态sql仅在表名、排序字段、where条件组合等无法静态预知时使用，须白名单校验对象名、参数化数据值，并用quotename和sp_executesql保障安全与性能。

动态 SQL 是存储过程中处理不确定条件、灵活表名或列名的关键手段，但用不好容易引发性能问题和 SQL 注入风险。核心原则是：能静态尽量静态，必须动态时再构造，且务必参数化、严格校验。

何时该用动态 SQL

静态 SQL 无法满足以下常见场景时才考虑动态拼接：

• 查询的表名由输入参数决定（如分表日志表 log_202401、log_202402）
• 排序字段或排序方向（ORDER BY @sortCol DESC）需运行时指定
• WHERE 条件数量和组合不固定（如多选筛选：地区、状态、时间范围可任意组合）
• 需要对不同结构的临时表或视图执行相似逻辑（如统一审计日志导出）

安全拼接：防注入 + 参数化

绝不能直接拼接用户输入值，尤其是表名、列名、操作符等非数据类内容。正确做法分两层：

• 白名单校验表/列名：用 CASE 或 if 判断输入是否在预设合法值中，否则报错退出
• 参数化数据值：所有用户传入的数据（如 @name、@minAmount）一律用 sp_executesql 配合参数占位符，不拼进字符串

示例（SQL Server）：

DECLARE @sql NVARCHAR(MAX), @params NVARCHAR(500) SET @sql = N'select * FROM ' + QUOTENAME(@tableName) +              N' WHERE status = @status' SET @params = N'@status TINYINT' EXEC sp_executesql @sql, @params, @status = @inputStatus

QUOTENAME() 安全转义对象名，sp_executesql 支持参数缓存执行计划，比 EXEC(@sql) 更高效安全。

避免常见陷阱

• 变量作用域失效：动态 SQL 内部声明的变量对外不可见，需用临时表或表变量传递结果
• 执行计划无法重用：频繁变化的 SQL 文本导致缓存污染，可考虑用 OPTION (RECOMPILE) 显式提示重新编译
• 调试困难：把最终生成的 @sql 打印出来（print @sql）再手动执行验证，是排查语法错误最直接方式
• 权限问题：调用者需有目标表的 SELECT 权限，而不仅是存储过程的 EXECUTE 权限

替代方案优先尝试

不是所有“看起来要动态”的需求都必须写动态 SQL：

• 固定几组条件？用 CASE WHEN 或多个 IF 分支走不同静态语句
• 排序字段有限？用 ORDER BY 后接多个带 CASE 的表达式模拟动态效果
• 多表联合？考虑用视图抽象或 union ALL + WHERE 过滤
• 复杂过滤？先用临时表/CTE 预处理数据，再用静态 SQL 查询

动态 SQL 是工具，不是默认解法。清晰的业务边界、合理的数据库设计，往往比一段“万能”动态语句更健壮易维护。