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

• 使用其完全限定名调用每个存储过程以提高性能:即服务器名、数据库名、架构(所有者)名和过程名。
• 使用sysmessage、sp_addmessage和占位符,而不是硬编码的错误消息。
• 使用sp_addmessage和sysmessages时,始终使用错误消息编号50001或更大。
• 对于RAISERROR,始终为错误消息提供介于11和16之间的严重性级别。
• 请记住,使用RAISERROR并不总是中止任何正在进行的批处理,即使在触发器上下文中也是如此。
• 拯救 @@error 在使用或查询局部变量之前,将其转换为局部变量。
• 在使用或查询局部变量之前,将@@rowcount保存到该局部变量。
• 对于存储过程,使用返回值仅指示成功/失败,而不指示任何其他/额外信息。
• 将ANSI_WARNINGS设置为ON-这会检测任何聚合赋值中的空值,以及任何超过字符或二进制列最大长度的赋值。
• XACT_ABORT ON or OFF . 无论你走哪条路,都要始终如一。
• 第一个错误时退出-这实现了KISS模型。

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

  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。

• 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的查询也会中止,但@错误为0!在这些情况下,您可能希望在启用SET XACT_ABORT的情况下运行。
• 如果要使用动态SQL,请尝试在每个批处理中仅使用一个SELECT,因为@error只保存最后执行的命令的状态。一批动态SQL中最有可能出现的错误是语法错误,而将XACT_ABORT设置为ON并不会处理这些错误。

我一直尝试使用的唯一技巧是:总是在靠近顶部的注释中包含示例用法。这对于测试您的SP也很有用。我喜欢包括最常见的示例,这样您甚至不需要SQL提示符或带有您最喜欢的调用的单独的.SQL文件,因为它就存储在服务器中(如果您存储了查看sp_的进程,sp_输出块或其他内容并获取一组参数,则这一点尤其有用)。

比如:

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

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

2. 如果要执行两次或两次以上的插入/更新/删除,请使用事务。

坏的:

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

那么,“最佳”解决方案的错误处理在哪里呢?你不需要。见 将XACT_中止设置为ON

• 统一命名存储过程。许多人使用前缀来标识它是一个存储过程,但不使用“sp_u2;”作为前缀,因为它是为主数据库指定的(无论如何在SQL Server中)
• 将NOCOUNT设置为on,因为这会减少可能返回值的数量
• 基于集合的查询通常比游标执行得更好。 This question
• 如果要为存储过程声明变量,请使用良好的命名约定,就像在任何其他类型的编程中一样。
• 使用SP的完全限定名调用SP,以消除关于应调用哪个SP的任何混淆,并帮助提高SQL Server性能;这样可以更容易地找到有问题的SP。

SQL Server Stored Procedures Optimization Tips

在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

这在很大程度上取决于您在存储过程中执行的操作。但是,如果您在一个过程中执行多个插入/更新或删除,则最好使用事务。这样,如果一个部分出现故障,其他部分将回滚,使数据库处于一致状态。

如果需要,在过程中写入检查,以确保最终结果是正确的。我是一名ETL专家,我总是在尝试将数据导入表之前编写程序,以使数据得到清理和规范化。如果您是从用户界面执行操作,那么在proc中执行此操作可能不太重要,尽管我会让用户界面在运行proc之前进行检查,以确保数据适合插入(例如检查以确保日期字段包含真实日期、所有必需字段都有值等)

我不喜欢动态SQL,我不喜欢在存储过程中使用它。如果您在现有进程中使用动态SQl,请输入一个调试标志,允许您打印SQl而不是执行它。然后在注释中输入您需要运行的最典型的案例。您会发现,如果这样做,您可以更好地维护该进程。

如果您使用的是case语句或If语句,请确保您已经完成了测试,这些测试将命中每个可能的分支。你不测试的是会失败的。

存储过程只是存储的T-SQL查询。因此,您真正需要做的是更加熟悉T-SQL以及各种函数和语法。更重要的是,从性能的角度来看,您需要确保您的查询和底层数据结构以允许良好性能的方式匹配。即,确保索引、关系、约束等在需要时得到实施。

了解如何使用性能调优工具,了解执行计划的工作方式,以及这类性质的事情是如何达到“下一个级别”的

在SQL Server 2008中,使用TRY…CATCH构造,您可以在T-SQL存储过程中使用该构造,通过在每个SQL语句后检查@错误(通常是GOTO语句的使用),为异常处理提供比以前版本的SQL Server更优雅的机制。

         BEGIN TRY
             one_or_more_sql_statements
         END TRY
         BEGIN CATCH
             one_or_more_sql_statements
         END CATCH

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

与执行的每条语句都会重置的@@error不同,error函数检索到的错误信息在TRY…CATCH语句的CATCH块范围内的任何位置都保持不变。这些函数可以将错误处理模块化为单个过程,这样您就不必在每个CATCH块中重复错误处理代码。

具有错误处理策略,并在所有SQL语句上捕获错误。

包括带有用户、日期/时间和sp用途的注释标题。
如果执行成功,则显式返回0(成功),否则返回其他值。

养成性能测试的习惯。对于文本案例,至少记录执行时间。

几乎从不从SP调用SP。可重用性与SQL不同。

1. 避免使用sp前缀
3. 尽量避免使用临时表
4. 尽量避免使用Select*from
6. 使用适当的索引

For more explanations and T-SQL code samples please check this post

以下是一些代码,用于证明SQL Server上没有多级回滚,并说明了如何处理事务:

BEGIN TRAN;

    SELECT @@TRANCOUNT AS after_1_begin;

BEGIN TRAN;

    SELECT @@TRANCOUNT AS after_2_begin;

COMMIT TRAN;

    SELECT @@TRANCOUNT AS after_1_commit;

BEGIN TRANSACTION;

    SELECT @@TRANCOUNT AS after_3_begin;

ROLLBACK TRAN;

    SELECT @@TRANCOUNT AS after_rollback;