编写sql存储过程的最佳实践是什么？

Question

我发现SQL存储过程非常有趣和有用。我已经编写了存储过程，但我希望为任何类型的需求编写精心设计、性能调优和简洁的SPs，并且希望了解存储过程的任何技巧或良好实践。如何从初学者到高级阶段编写存储过程？

更新:从评论中发现，我的问题应该更加具体。每个人的袖子上都有一些技巧，我期待着他们在代码中使用的SPs这样的技巧和实践，这使他们与其他人区别开来，更重要的是，提高了编写和使用存储过程的效率。

Answer 1

以下是我的存储过程错误处理指南。

• 使用其完全限定名调用每个存储过程以提高性能:即服务器名称、数据库名称、模式(所有者)名称和过程名称。
• 在创建每个存储过程的脚本中，显式指定允许哪些角色执行过程(如公共角色或其他角色)。
• 使用sysmessage、sp_addmessage和占位符，而不是硬编码错误消息。
• 使用sp_addmessage和sysmessages时，始终使用错误消息号50001或更高。
• 使用RAISERROR，始终为警告消息提供严重性级别<= 10。
• 对于RAISERROR，总是为错误消息提供11到16之间的严重级别。
• 记住，使用RAISERROR并不总是中止正在进行的任何批处理，即使在触发器上下文中也是如此。
• 在使用@@错误或询问它之前将它保存到局部变量中。
• 在使用或询问@@rowcount之前，将其保存到局部变量中。
• 对于存储过程，只使用返回值指示成功/失败，而不是任何其他/额外信息。
• 存储过程的返回值应设置为0以表示成功，非零表示失败。
• 设置ANSI_WARNINGS ON -这将检测任何聚合赋值中的空值，以及任何超过字符或二进制列最大长度的赋值。
• 设置NOCOUNT，有很多原因。
• 仔细考虑是否需要断断续续。无论你走哪条路，都要始终如一。
• 在第一个错误上退出-这实现了接吻模型。
• 在执行存储过程时，始终同时检查@@error和返回值。例如： EXEC @err = AnyStoredProc @value SET @save_error = @save_error -- NULLIF表示，如果@err为0，这与null相同-如果@err为0，这与空
• 当执行导致错误的本地存储过程时，执行回滚，因为过程可能已经启动了它没有提交或回滚的事务。
• 不要仅仅因为您还没有启动一个事务就认为没有任何活动事务-调用方可能已经启动了一个事务。
• 理想情况下，避免对调用方启动的事务执行回滚操作，因此请检查@@trancount。
• 但是在触发器中，始终要执行回滚，因为您不知道调用方是否启动了一个活动事务(因为@@trancount总是>= 1)。
• 始终在下列语句之后存储和检查@@error： 插入、删除、更新SELECT到存储过程调用动态SQL提交事务声明并打开游标从游标WRITETEXT和UPDATETEXT获取
• 如果在进程-全局游标(默认)上声明游标失败，则发出一条语句来释放游标。
• 注意UDF中的错误。当UDF中发生错误时，函数的执行将立即中止，调用UDF的查询也会中止--但是@@error是0！在这种情况下，您可能希望使用SET XACT_ABORT运行。
• 如果要使用动态SQL，请尝试在每个批处理中只有一个选择，因为@@error只保存执行的最后一个命令的状态。来自一批动态SQL的最可能的错误是语法错误，而这些错误不是由SET XACT_ABORT ON处理的。

Answer 2

我一直试图使用的唯一技巧是:始终在顶部的注释中包含一个示例用法。这对于测试你的SP也很有用。我喜欢包含最常见的例子--那么您甚至不需要使用SQL提示符或带有您最喜欢的调用的单独.sql文件，因为它就存储在服务器中(如果您存储了查看块或其他方面的sp_who输出并接受一堆参数的procs )，这将特别有用。

类似于：

/*
    Usage:
    EXEC usp_ThisProc @Param1 = 1, @Param2 = 2
*/

然后，要测试或运行SP，只需在脚本中突出显示该部分并执行。

Answer 3

1. 始终使用SET NOCOUNT
2. 如果要执行两个或多个插入/更新/删除操作，请使用事务处理。
3. 永远不要把你的程序命名为'sp_‘。Server将首先查看主数据库，而不是查找它，然后再查看数据库。如果您以不同的名称命名procs，Server将首先查看数据库。

坏：

SET NOCOUNT ON
BEGIN TRAN
  INSERT...
  UPDATE...
COMMIT

更好，但看上去很混乱，而且编码很痛苦：

SET NOCOUNT ON
BEGIN TRAN
  INSERT...
  IF @ErrorVar <> 0
  BEGIN
      RAISERROR(N'Message', 16, 1)
      GOTO QuitWithRollback
  END

  UPDATE...
  IF @ErrorVar <> 0
  BEGIN
      RAISERROR(N'Message', 16, 1)
      GOTO QuitWithRollback
  END

  EXECUTE @ReturnCode = some_proc @some_param = 123
  IF (@@ERROR <> 0 OR @ReturnCode <> 0)
       GOTO QuitWithRollback 
COMMIT
GOTO   EndSave              
QuitWithRollback:
    IF (@@TRANCOUNT > 0)
        ROLLBACK TRANSACTION 
EndSave:

好：

SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRY
    BEGIN TRAN
    INSERT...
    UPDATE...
    COMMIT
END TRY
BEGIN CATCH
    IF (XACT_STATE()) <> 0
        ROLLBACK
END CATCH

最佳：

SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
    INSERT...
    UPDATE...
COMMIT

那么，“最佳”解决方案的错误处理在哪里呢？你不需要任何东西。请参阅SET XACT_ABORT ON，这意味着如果有任何错误，则执行自动回滚。代码更干净、更容易阅读、更易于编写、更少的buggy。错误少了，因为没有机会错过错误条件，因为Server现在为您做了这件事。