SQL 存储过程创建与调用优化技巧方法

高效安全的存储过程需关注五点：参数设计（明确类型、避免隐式转换）、执行计划复用（防参数嗅探、合理使用recompile）、错误处理（try…catch+throw）、权限与sql注入防护（禁用exec、白名单校验）、调用方式优化（静态sql优先、select返回结果集）。

SQL 存储过程的创建与调用本身不难，但写得高效、安全、易维护，需要关注几个关键点：参数设计、执行计划复用、错误处理、权限控制和调用方式选择。

参数设计要明确类型与默认值

避免使用 varchar 无长度定义（如 varchar 而非 varchar(50)），这会导致隐式转换，影响索引使用和执行计划稳定性。输入参数尽量设为 NOT NULL，必要时提供合理默认值，减少调用方传参负担。

• 用 = NULL 定义可选参数，内部用 IS NULL 判断而非 = NULL
• 输出参数只用于返回简单标量值；复杂结果集优先用 SELECT 返回，更利于客户端绑定
• 大数据量筛选场景下，慎用 LIKE '%xxx%' 类参数——它几乎必然导致全表扫描

避免动态 SQL 带来的性能与安全风险

除非必须（如表名/列名由参数决定），否则优先用静态 SQL。拼接字符串构造 SQL 不仅难以缓存执行计划，还容易引发 SQL 注入——尤其当参数来自外部输入时。

• 确需动态执行时，统一用 sp_executesql（支持参数化），禁用 EXEC(@sql)
• 若参数参与 WHERE 条件且存在多种组合，可用 OR + IS NULL 或 CASE 构建“智能查询”，保持语句结构稳定
• 对用户可控的表名/列名，应严格白名单校验（如查系统视图 sys.tables），不可直接拼接

调用前检查执行计划与参数嗅探问题

存储过程第一次执行时生成的执行计划，可能因首次传入参数的分布特征而“固化”低效路径（即参数嗅探）。后续不同参数调用仍沿用该计划，造成性能抖动。

• 用 OPTION (RECOMPILE) 强制每次重编译（适合参数值差异大、执行频次不高的场景）
• 对高频调用过程，可加 WITH RECOMPILE 创建，或在调用时加 OPTION (OPTIMIZE FOR (@p = '典型值'))
• 通过 sys.dm_exec_query_stats 和 sys.dm_exec_sql_text 查看历史执行耗时与计划重用情况

错误处理必须显式且可追踪

不要依赖客户端捕获 RAISERROR 就完事。存储过程中应统一用 TRY…CATCH 捕获异常，并记录关键上下文（如出错行号、参数值摘要），再抛出带业务含义的错误号。

• 在 CATCH 块中调用 ERROR_LINE()、ERROR_MESSAGE() 获取详细信息
• 避免裸写 RETURN -1，改用 THROW 50001, '业务失败原因', 1 提升可读性
• 事务内出错时，先判断 XACT_STATE() 再决定是 ROLLBACK 还是 COMMIT

不复杂但容易忽略。核心是让存储过程像函数一样有契约、有边界、有反馈，而不是把 SQL 逻辑堆进去就完事。