编写sql存储过程的最佳实践是什么 [英] What are the best practices in writing a sql stored procedure

问题描述

我发现 SQL 存储过程非常有趣和有用.我已经编写了存储过程，但我想为任何类型的需求编写精心制作、性能调优且简洁的 SP，并且还想了解存储过程的任何技巧或良好实践.在编写存储过程时，我如何从初学者过渡到高级阶段?

I found that SQL stored procedures are very interesting and useful. I have written stored procedures but i want to write well crafted, good performance tuned and concise SPs for any sort of requirement and also would love to learn about any tricks or good practices for stored procedures. How do i move from the beginner to the advanced stage in writing stored procedures?

更新:从评论中发现我的问题应该更具体.每个人都有一些技巧，我期待他们在他们的代码中使用这样的技巧和实践来区分他们，更重要的是提高编写和使用存储过程的生产力.

推荐答案

这是我的存储过程错误处理指南.

Here are my stored procedure error-handling guidelines.

• 使用其完全限定名称调用每个存储过程以提高性能:即服务器名称、数据库名称、架构(所有者)名称和过程名称.
• 在创建每个存储过程的脚本中，明确指定允许哪些角色执行该过程，例如 public 或其他.
• 使用 sysmessage、sp_addmessage 和占位符而不是硬编码的错误消息.
• 使用 sp_addmessage 和 sysmessages 时，请始终使用 50001 或更大的错误消息号.
• 对于 RAISERROR，始终为警告消息提供 <= 10 的严重性级别.
• 对于 RAISERROR，始终为错误消息提供 11 到 16 之间的严重性级别.
• 请记住，即使在触发器上下文中，使用 RAISERROR 并不总是中止任何正在进行的批处理.
• 在使用之前将 @@error 保存到局部变量，或者询问它.
• 在使用或查询之前将@@rowcount 保存到局部变量.
• 对于存储过程，使用返回值仅表示成功/失败，而不是任何其他/额外信息.
• 存储过程的返回值应设置为 0 表示成功，非零表示失败.
• 将 ANSI_WARNINGS 设置为 ON - 这会检测任何聚合分配中的空值，以及任何超过字符或二进制列最大长度的分配.
• 设置 NOCOUNT ON，原因有很多.
• 仔细考虑您是否想要XACT_ABORT 开启或关闭.无论你走哪条路，都要保持一致.
• 出现第一个错误时退出 - 这实现了 KISS 模型.

• 在执行存储过程时，始终检查@@error 和返回值.例如:

• Call each stored procedure using its fully qualified name to improve performance: that's the server name, database name, schema (owner) name, and procedure name.
• In the script that creates each stored procedure, explicitly specify which roles are allowed to execute the procedure ,eg public or whatever.
• Use sysmessage, sp_addmessage, and placeholders rather than hard-coded error messages.
• When using sp_addmessage and sysmessages, always use error message number of 50001 or greater.
• With RAISERROR, always supply a severity level <= 10 for warning messages.
• With RAISERROR, always supply a severity level between 11 and 16 for error messages.
• Remember that using RAISERROR doesn't always abort any batch in progress, even in trigger context.
• Save @@error to a local variable before using it or interrogating it.
• Save @@rowcount to a local variable before using it or interrogating it.
• For a stored procedure, use the return value to indicate success/failure only, not any other/extra information.
• Return value for a stored procedure should be set to 0 to indicate success, non-zero to indicate failure.
• Set ANSI_WARNINGS ON - this detects null values in any aggregate assignment, and any assignment that exceeds the maximum length of a character or binary column.
• Set NOCOUNT ON, for many reasons.
• Think carefully about whether you want XACT_ABORT ON or OFF. Whichever way you go, be consistent.
• Exit on the first error - this implements the KISS model.

• When executing a stored procedure, always check both @@error and the return value. For example:

EXEC @err = AnyStoredProc @value
SET  @save_error = @@error
-- NULLIF says that if @err is 0, this is the same as null
-- COALESCE returns the first non-null value in its arguments
SELECT @err = COALESCE( NULLIF(@err, 0), @save_error )
IF @err <> 0 BEGIN 
    -- Because stored proc may have started a tran it didn't commit
    ROLLBACK TRANSACTION 
    RETURN @err 
END

当执行导致错误的本地存储过程时，请执行回滚，因为该过程可能启动了一个未提交或未回滚的事务.

不要仅仅因为您还没有开始交易就假设没有任何活动的交易 - 调用者可能已经开始了.

理想情况下，避免对调用者启动的事务进行回滚 - 因此请检查 @@trancount.

但在触发器中，始终执行回滚，因为您不知道调用者是否发起了活动事务(因为 @@trancount 始终 >= 1).

始终在以下语句后存储和检查@@error:

When executing a local stored procedure that results in an error, do a rollback because it's possible for the procedure to have started a transaction that it didn't commit or rollback.

Don't assume that just because you haven't started a transaction, there isn't any active transaction - the caller may have started one.

Ideally, avoid doing rollback on a transaction that was started by your caller - so check @@trancount.

But in a trigger, always do rollback, as you don't know whether the caller initiated an active transaction (because @@trancount is always >= 1).

Always store and check @@error after the following statements:

INSERT, DELETE, UPDATE
SELECT INTO
Invocation of stored procedures
invocation of dynamic SQL
COMMIT TRANSACTION
DECLARE and OPEN CURSOR
FETCH from cursor
WRITETEXT and UPDATETEXT

如果 DECLARE CURSOR 在进程全局游标(默认)上失败，则发出一条语句来取消分配游标.

小心 UDF 中的错误.当 UDF 中发生错误时，函数的执行会立即中止，调用 UDF 的查询也会中止 - 但 @@error 为 0！在这些情况下，您可能希望使用 SET XACT_ABORT ON 运行.

如果要使用动态 SQL，请尝试在每个批处理中只使用一个 SELECT，因为 @@error 只保存最后执行的命令的状态.一批动态 SQL 中最可能的错误是语法错误，SET XACT_ABORT ON 不会处理这些错误.

这篇关于编写sql存储过程的最佳实践是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！