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

Question

关闭。这个问题是 opinion-based 。目前不接受答案。

---

想要改进这个问题吗？更新问题，以便 editing this post 可以用事实和引文来回答它。 .

已关闭 3 年前。

Improve this question

我发现 SQL 存储过程非常有趣且有用。我已经编写了存储过程，但我想为任何类型的需求编写精心设计、性能调整良好且简洁的 SP，并且也很想了解存储过程的任何技巧或良好实践。如何从编写存储过程的初级阶段进入高级阶段？

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

Answer 1

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

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

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

  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:

  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 不会处理这些错误。