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

Question

我发现SQL存储过程非常有趣和有用。我写了存储过程，但我想为任何需求编写精心制作的、性能优化良好且简洁的SPs，并且也很乐意学习有关存储过程的任何技巧或最佳实践。如何从初学者阶段转变为编写存储过程的高级阶段？

更新：从评论中发现我的问题应该更具体。每个人都有一些他们在代码中使用的SP的技巧，我期望得到这些技巧和实践，这些技巧和实践使他们与其他人不同，并且更重要的是增强了编写和使用存储过程的生产力。

- blntechie

---

这就像是说，“我已经写了一些故事，但我想要写出精心打造的畅销小说，适合所有类型的读者，并且也想学习有关写作故事的任何技巧或好的实践方法。” 你可能需要更具体地说明你正在寻找什么... - Kevin Fairchild

同意，更具体的问题会更好。 - ahockley

Answer 1

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

• 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
  

• 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
  

• If DECLARE CURSOR fails on a process-global cursor (the default), issue a statement to deallocate the cursor.
• Be careful with an error in a UDF. When an error occurs in a UDF, execution of the function is aborted immediately and so is the query that invoked the UDF - but @@error is 0! You may want to run with SET XACT_ABORT ON in these circumstances.
• If you want to use dynamic SQL, try to have only a single SELECT in each batch because @@error only holds the status of the last command executed. The most likely errors from a batch of dynamic SQL are syntax errors, and these aren't taken care of by SET XACT_ABORT ON.

- RoadWarrior

---

好的长列表。感谢指导。 - Shiham

使用SQL 2012，您可以使用XACT_STATE()来决定是否需要回滚，而不是检查@@error。http://technet.microsoft.com/zh-cn/library/ms189797.aspx - nojetlag

同时避免使用插入执行模式，因为它会阻止错误处理[如果在打开事务期间子过程失败]（https://dba.stackexchange.com/questions/220847）。 - Elaskanator

如何在单个服务器上的多个相同数据库实例（当然名称不同）以及多个服务器上实现“使用其完全限定名称调用每个存储过程”？ - Elaskanator

Answer 2

我经常使用的一个技巧是：在接近顶部的注释中始终包含一个使用示例。这对于测试你的存储过程也很有用。我喜欢包含最常见的示例 - 这样你甚至不需要 SQL Prompt 或一个单独的 .sql 文件来调用它，因为它存储在服务器中（如果你有存储过程查看 sp_who 输出以查找阻塞或其他内容，并且需要大量参数，则特别有用）。

比如：

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

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

- Cade Roux

Answer 3

1. 始终使用 SET NOCOUNT ON。
2. 如果您将执行两个或更多的插入/更新/删除操作，请使用事务。
3. 永远不要将存储过程命名为 'sp_'。 SQL Server首先会查找master数据库，未找到后才会查找您的数据库。如果您以不同的名称命名存储过程，则SQL 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，这意味着如果有任何错误，则执行自动回滚。代码更加简洁易读、易写、更加健壮。更加健壮是因为现在 SQL Server 会为你处理错误条件，不存在遗漏错误条件的可能性。

- Simon Hughes

---

亲爱的Simon Hughes， 您能否给出一个带有演示插入语句的示例，从编写过程的开始到结束呢？ - sidhewsar

Answer 4

这是一个非常普遍的问题，但是以下是几个建议：

• 一定要有规律地命名存储过程。许多人使用前缀来标识这是一个存储过程，但不要使用“sp_”作为前缀，因为这是指定给主数据库的（在SQL Server中）。
• 设置NOCOUNT，因为这会减少可能的返回值数量。
• 基于集合的查询通常比游标执行得更好。这个问题详细介绍了这个问题。
• 如果你要为存储过程声明变量，请使用良好的命名规范，就像在任何其他类型的编程中一样。
• 使用完全限定名称调用存储过程，以消除对应该调用哪个存储过程的任何疑惑，并提高SQL Server的性能；这使得更容易找到相关的存储过程。

当然还有更多内容。以下链接提供了更多信息： SQL Server 存储过程优化技巧

- AR

Answer 5

在SQL Server中，我总是会写一个语句，以便在我开发过程中轻松地重新创建存储过程，如果存在则将其删除。类似于：

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'usp') AND type in (N'P', N'PC'))
DROP PROCEDURE usp

- Scott

---

你可以使用以下代码简化这个过程： IF OBJECT_ID(N'dbo.usp') IS NOT NULL DROP PROCEDURE dbo.usp GOCREATE PROCEDURE dbo.usp ... - Tom

是的，alter也可以做同样的事情，但如果您将其写为alter并首次放置在服务器上，则会失败。无论过程是否已经存在，此过程都将起作用。 - HLGEM

问题在于，当你删除存储过程时，你会失去所有相关的设置。ALTER PROC保留了所有安全设置，因此首选使用ALTER。 - darlove

在我看来，更好的方法是修改您的代码，在开头包含相反的检查。如果它不存在，则快速创建它。然后添加您的权限，然后在其下方添加Alter语句。这样，您可以随时在任何地方运行它，而不需要进行不必要的删除/重新创建操作，并且不会丢失任何设置。 - Jon

Answer 6

这在很大程度上取决于存储过程中的具体操作。然而，如果您在一个存储过程中进行多个插入/更新或删除操作，使用事务是一个好主意。这样，如果其中一部分失败，其他部分将被回滚，使您的数据库保持一致状态。

写入数据库时最重要的两件事情（因此使用执行除选择之外的操作的存储过程时）是数据完整性和性能。没有数据完整性，您只有一个包含垃圾数据且无用的数据库。没有性能，您将没有用户（如果他们是外部客户）或不满意的用户（如果他们被强制使用您的产品，通常是没有选择去其他地方的内部用户）。这两种情况对您的职业生涯都不利。因此，在编写存储过程时，请确保首先确保数据将正确输入到数据库中，并且如果某个操作出现问题，则会失败。

如果需要，在存储过程中编写检查以确保最终结果是正确的。我是ETL专家，我总是编写我的存储过程以在尝试将数据导入表之前清理和规范化数据。如果您正在从用户界面执行操作，则可能不需要在存储过程中执行此操作，尽管我建议在运行存储过程之前，用户界面应该进行检查以确保插入的数据是好的（例如检查日期字段是否包含真实日期，所有必需的字段是否有值等）。

如果您正在编写将大量数据放入表中的存储过程，则最好有一种方法在最终确定结果之前测试这些结果。您会惊讶于从客户和供应商那里获得的数据导入垃圾数据。我们使用测试标志编写所有导入存储过程。这样，您可以返回选择数据而不执行操作，以便您可以提前查看您将影响的内容。

我不喜欢动态SQL，并且不愿在存储过程中使用它。如果您被现有存储过程卡住了，必须使用动态SQL，请添加一个调试标志，以允许您打印SQL而不是执行它。然后在注释中加入最常见的情况。如果这样做，您会发现可以更好地维护存储过程。

不要仅仅因为您想重用仅适用于一条记录的另一个存储过程而在游标中执行操作。导致性能问题的代码重用是不好的事情。

如果您使用case语句或if语句，请确保已进行测试以涵盖每个可能的分支。您没有测试的那个分支将失败。

- HLGEM

---

不要因为想重用只能逐条处理记录的其他存储过程而在游标中执行操作。这在一开始可能有效，但是在实际数据量下会导致整个服务器死机。 - Elaskanator

Answer 7

这不是一个可以直接回答的问题，需要更多的信息，但是有一些通用的经验法则确实适用。

存储过程只是存储的T-SQL查询。因此，你需要更加熟悉T-SQL和各种函数和语法。从性能的角度来看，你需要确保查询和底层数据结构匹配，以便获得良好的性能。例如，确保在需要时实现索引、关系、约束等。

了解如何使用性能调整工具，了解执行计划的工作原理等方面，是你达到“下一个级别”的方法。

- Mitchel Sellers

---

存储过程仅仅是存储的 T-SQL 查询。存储过程远不止于此。视图只是存储的 SQL 查询。 - darlove

Answer 8

使用SQL Server 2008时，可以使用TRY...CATCH结构，在T-SQL存储过程中使用它来提供比以前版本的SQL Server更优雅的异常处理机制。在每个SQL语句之后，通过检查@@ERROR（通常使用GOTO语句）来实现。

         BEGIN TRY
             one_or_more_sql_statements
         END TRY
         BEGIN CATCH
             one_or_more_sql_statements
         END CATCH

在CATCH块中，您可以使用以下错误函数来捕获触发CATCH块的错误信息，

         ERROR_NUMBER()
         ERROR_MESSAGE()
         ERROR_SEVERITY()
         ERROR_STATE()
         ERROR_LINE()
         ERROR_PROCEDURE()

与每个执行的语句重置的@@error不同，由错误函数检索到的错误信息在TRY...CATCH语句的CATCH块范围内始终保持不变。这些函数可以将错误处理模块化为单个过程，因此您无需在每个CATCH块中重复错误处理代码。

- nk2

Answer 9

基础内容：

制定错误处理策略，并在所有 SQL 语句中捕获错误。
决定存储过程源代码控制的使用策略。
包括带有用户、日期/时间和 sp 目的的注释头。
明确返回 0（成功）以表示成功执行，否则返回其他内容。
对于非平凡的存储过程，包括一个或多个测试用例和预期结果的描述。
养成性能测试的习惯。对于文本案例，至少记录执行时间。
了解显式事务并使用它们。
几乎不要从存储过程中调用 SP。在 SQL 中，可重用性是一种不同的游戏。

- doofledorfer

---

为什么不从存储过程中调用其他存储过程？我经常出于必要而这样做；如果不这样做，就会违反DRY原则...？ - Steven A. Lowe

DRY原则适用于过程式代码，而不是声明式代码。如果存储过程中有过程式逻辑，建议将其移出到另一个层次，这样更加合理。 - dkretz

另一个强烈的反感。多级回滚事务非常混乱。 - dkretz

@dkretz：SQL Server 上没有多级回滚。在任何一个会话中，只能有1个或0个事务处于活动状态。当有一个事务正在运行时，任何新的 BEGIN TRAN 都不会起作用；它只是将 @@TRANCOUNT 加 1，但仍然只有1个事务在运行。在会话中从任何地方执行 ROLLBACK TRAN 都会回滚当前活动的事务并将 @@TRANCOUNT 设置为0（即没有事务处于活动状态）。 - darlove

Answer 10

以下是一些最佳实践：

1. 避免使用_sp前缀
2. 包括SET NOCOUNT ON语句
3. 尽量避免使用临时表
4. 尽量避免使用Select * from
5. 尽量避免使用游标
6. 使用适当的索引
7. 适当的错误处理

欲了解更多说明和T-SQL代码示例，请查看此文章

- nagnath

---

为什么我们应该避免使用临时表？这不是明智的建议。如果正确使用，临时表是我们工具库中最好的东西之一。 - darlove