动态sql需兼顾安全、可测、可观测与性能：强制参数化+白名单校验防注入；执行前print/select输出语句并手动验证；嵌入注释标记+扩展事件实现运行时追踪；复用模板、避免局部变量、按需recompile优化性能。
动态 SQL 是数据库开发中灵活处理不确定查询结构的关键技术，但执行效率低、调试困难、安全风险高是常见痛点。核心在于：语句拼接要安全，执行前需验证，运行时得可追踪。
安全拼接：避免 SQL 注入的硬性规则
所有用户输入必须经过参数化处理，禁止直接字符串拼接。即使字段名、表名等无法参数化的部分，也要严格白名单校验。
- 用 sp_executesql 替代 EXEC，强制使用参数占位符（如 @p1, @p2）传递值
- 若需动态列名或表名，先查系统视图（如 sys.tables / sys.columns）确认存在，再通过 CASE 或白名单映射转换
- 对输入字符串做双重过滤：先 Trim 去空格，再用 QUOTENAME() 包裹标识符（如 QUOTENAME(@tablename)）
执行前验证：让 SQL “可见可测”
不要跳过打印和语法检查环节。把最终生成的 SQL 字符串显式输出，是定位问题最快的方式。
- 在 EXEC sp_executesql 前，用 PRINT 或 SELECT 输出完整语句（注意：PRINT 截断 4000 字符，长语句改用 SELECT CAST(@sql AS XML) 避免截断）
- 将生成的 SQL 复制到新查询窗口，手动执行一次——能快速发现拼写错误、括号不匹配、引号嵌套混乱等问题
- 对复杂动态逻辑，拆成小段变量分步构建（如 @sql_base + @sql_where + @sql_order），每段都 PRINT 核查
运行时追踪：让执行过程“有据可查”
动态 SQL 默认不进执行计划缓存详情，也不出现在 Profiler 的常规事件里，需主动增强可观测性。
- 在动态 SQL 中嵌入注释标记，例如 /* Proc:usp_SearchUser | User:John */，便于在 DMV（如 sys.dm_exec_query_stats）中筛选定位
- 结合 SET STATISTICS XML ON 或捕获实际执行计划（通过 SSMS 执行时勾选“包含实际执行计划”）分析性能瓶颈
- 记录关键上下文：用扩展事件（XEvent）监听 sql_batch_completed 事件，并添加 predicate 过滤含特定注释的语句
性能优化：减少编译开销与计划污染
高频执行的动态 SQL 容易引发重复编译或缓存膨胀，需控制参数敏感度和重用粒度。
- 尽量复用同一模板：用 NULL 参数表示“忽略条件”，而非拼接不同 WHERE 子句（如 WHERE (@name IS NULL OR name = @name)）
- 避免在动态 SQL 中使用 local variables 赋值后参与条件判断——这会导致每次执行都生成新计划；改用参数传入
- 对固定模式但数据量差异大的场景，考虑 OPTION (RECOMPILE) 显式重编译，比依赖缓存更可控
动态 SQL 不是黑盒，关键是把“生成—验证—执行—反馈”四个环节显性化。写得谨慎，看得清楚，查得及时，性能和安全自然可控。
