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

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?

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

Update: Found from comments that my question should be more specific. Everyone has some tricks upon their sleeves and I was expecting such tricks and practices for SPs which they use in their code which differentiates them from others and more importantly spruce up the productivity in writing and working with stored procedures.

更新:从评论中发现我的问题应该更具体。每个人都有自己的小把戏，我希望这些小把戏和实践能在他们的代码中使用，将他们和其他人区分开来，更重要的是，在编写和使用存储过程时提高生产率。

9 个解决方案

#1

Here are my stored procedure error-handling guidelines.

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

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.
在创建每个存储过程的脚本中，显式指定允许执行该过程的角色(如public或其他)。
Use sysmessage, sp_addmessage, and placeholders rather than hard-coded error messages.
使用sysmessage、sp_addmessage和占位符，而不是硬编码的错误消息。
When using sp_addmessage and sysmessages, always use error message number of 50001 or greater.
在使用sp_addmessage和sysmessage时，总是使用50001或以上的错误消息号。
With RAISERROR, always supply a severity level <= 10 for warning messages.
对于RAISERROR，始终为警告消息提供一个严重级别<= 10。
With RAISERROR, always supply a severity level between 11 and 16 for error messages.
对于RAISERROR，始终为错误消息提供11到16之间的严重级别。
Remember that using RAISERROR doesn't always abort any batch in progress, even in trigger context.
请记住，使用RAISERROR并不总是终止正在进行的任何批处理，即使是在触发器上下文中。
Save @@error to a local variable before using it or interrogating it.
在使用或查询本地变量之前，将@@error保存到本地变量。
Save @@rowcount to a local variable before using it or interrogating it.
在使用或查询本地变量之前，将@ @@rowcount保存到本地变量中。
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.
存储过程的返回值应该设置为0表示成功，非0表示失败。
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.
设置ansi_warning ON——它检测任何聚合赋值中的null值，以及任何超过字符或二进制列最大长度的赋值。
Set NOCOUNT ON, for many reasons.
设置不依赖，有很多原因。
Think carefully about whether you want XACT_ABORT ON or OFF. Whichever way you go, be consistent.
仔细考虑您是否想要执行XACT_ABORT，无论您选择哪一种方式，都要保持一致。
Exit on the first error - this implements the KISS model.
在第一个错误上退出—这实现了KISS模型。

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

执行存储过程时，总是同时检查@@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

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.
理想情况下，避免对由调用者启动的事务执行回滚——因此请检查@@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).
但是在触发器中，总是要回滚，因为您不知道调用者是否发起了一个活动事务(因为@@trancount总是>= 1)。

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

在下列语句后，始终存储和检查@@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

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.
小心UDF中的错误。当UDF中发生错误时，函数的执行立即被中止，调用UDF的查询也是如此——但是@@error是0!在这些情况下，您可能希望使用SET XACT_ABORT运行。
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.
如果您想使用动态SQL，请尝试在每个批处理中只有一个SELECT，因为@@error只保存执行的最后一个命令的状态。这批动态SQL中最可能出现的错误是语法错误，这些错误不会被设置XACT_ABORT处理。

#2

The only trick I always try to use is: Always include an example usage in a comment near the top. This is also useful for testing your SP. I like to include the most common examples - then you don't even need SQL Prompt or a separate .sql file with your favorite invocation, since it's stored right there in the server (this is expecially useful if you have stored procs that look at sp_who output for blocks or whatever and take a bunch of parameters).

我一直尝试使用的惟一技巧是:在顶部附近的注释中包含一个示例用法。这也是用于测试您的SP。我喜欢包括最常见的例子,那么你甚至不需要SQL提示或与你最喜欢的一个单独的. SQL文件调用,因为它是存储在服务器(这是尤其有用的如果你有存储效果,看看sp_who输出块什么的,一堆参数)。

Something like:

喜欢的东西:

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

Then to test or run the SP, you simply highlight that section in your script and execute.

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

#3

This is a very general question, but here are a couple of pieces of advice:

这是一个非常普遍的问题，但这里有一些建议:

Name your stored procedures consistently. Many use a prefix to identify that it's a stored procedure, but don't use 'sp_' as the prefix as that's designated for the Master databae (in SQL Server anyway)
始终如一地命名存储过程。许多人使用前缀来标识它是一个存储过程，但是不要使用“sp_”作为前缀，因为它是为主数据库指定的(无论如何，在SQL Server中)
Set NOCOUNT on, as this reduces the number of possible return values
设置NOCOUNT，因为这会减少可能返回值的数量
Set-based queries often perform better than cursors. This question gets into this in much more detail.
基于集合的查询通常比游标执行得更好。这个问题涉及到更多的细节。
If you're DECLARE'ing variables for your stored procedure, use good naming conventions just as you would/should in any other kind of programming.
如果您要为存储过程声明变量，请使用良好的命名约定，就像在任何其他类型的编程中一样。
Call SPs using their fully-qualified name to eliminate any confusion about which SP should be called, and to help boost SQL Server performance; this makes it easier to find the SP in question.
使用完全限定的名称调用SPs，以消除关于应该调用哪个SP的任何混淆，并有助于提高SQL服务器性能;这样更容易找到有问题的SP。

There's much more, of course. Here's are a link with more: SQL Server Stored Procedures Optimization Tips

当然还有更多。下面是与更多的链接:SQL Server存储过程优化技巧。

#4

Always use SET NOCOUNT ON
总是使用SET NOCOUNT ON
If you are going to perform two or more inserts/updates/deletes, please use a transaction.
如果要执行两个或多个插入/更新/删除操作，请使用事务。
Never name your procs 'sp_'. SQL Server will look in the master database first, not find it, then look in your database second. If you name your procs differently, SQL Server will look in your database first.
永远不要给你的产品命名为sp_。SQL Server将首先查找主数据库，而不是查找它，然后再查找数据库。如果您以不同的方式命名您的procs, SQL Server将首先查看您的数据库。

Bad:

缺点:

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

Better, but looks messy and a major pain to code:

更好，但是看起来很乱，代码也很麻烦:

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:

Good:

好:

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

Best:

最好的:

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

So where is the error handling on the 'Best' solution? You don't need any. See the SET XACT_ABORT ON, that means perform an automatic rollback if there are any errors. The code is cleaner and easier to read, easier to write, and less buggy. Less buggy because there is no chance of missing an error condition as SQL Server now does this for you.

那么，“最佳”解决方案的错误处理在哪里呢?你不需要任何。查看SET XACT_ABORT，这意味着如果出现任何错误，就执行自动回滚。代码更简洁，更容易读，更容易写，bug更少。更少的bug，因为SQL Server现在不会丢失错误条件。

#5

In SQL Server I always put a statement that will drop the procedure if it exists so I can easily hit re-create the procedure while I am developing it. Something like:

在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

#6

This is not a question that can be answered directly without more information, but a few general rules of thumb really apply.

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

Stored procedures are simply T-SQL queries that are stored. Therefore, becoming more familiar with T-SQL and the various functions and syntaxes is really what you need to do. And even more so from a performance standpoint you will need to ensure that your queries and the underlying data structures match in a manner that allow for good performance. IE, ensure that indexes, relationships, constraints etc are implemented where needed.

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

Understanding how to use performance tuning tools, understaning how execution plans work, and things of that nature are how you get to that "next level"

了解如何使用性能调优工具，了解执行计划是如何工作的，以及这些特性是如何达到“下一阶段”的

#7

This depends greatly on what you are doing in the stored procs. However, it is a good idea to use transactions if you are doing multiple inserts/updates or deletes in one proc. That way if one part fails, the other parts are rolled back leaving your database in a consistent state.

这在很大程度上取决于您在存储过程中正在做什么。但是，如果您在一个proc中执行多个插入/更新或删除操作，那么最好使用事务。

The two most important things to consider when writing to a database (and thus when using a stored proc that performs an action other than select) are data integrity and performance. Without data integrity, you just have a database that contains garbage and is useless. Without performacne you will have no users (if they are outside clients) or unhappy users (if they are mandated to use your product, usually internal users who have no choice to go elsewhere). Neither one is good for your career. So in writing a stored proc, make sure you first ensure that data will be entered correctly into the database and that it will fail if there is a problem in one part of the action.

在向数据库写入时(因此在使用执行除select之外的操作的存储proc时)需要考虑的两个最重要的事项是数据完整性和性能。如果没有数据完整性，您只有一个包含垃圾的数据库，而且是无用的。如果没有performacne，你将没有用户(如果他们是外部客户)或不满意的用户(如果他们被要求使用你的产品，通常是内部用户，他们没有选择去别的地方)。两者都不适合你的职业。因此，在编写存储的proc时，首先要确保数据将被正确地输入到数据库中，如果操作的某个部分出现问题，那么它将失败。

If need be write checks into the proc to ensure your final result will be correct. I'm an ETL specialist and I always write my procs to make data is cleaned and normalized before I try to import it into my tables. If you are doing things from the user interface this might not be so important to do inthe proc, although I would have the user inteface do checks before even running the proc to ensure the data is good for the insert (things like checking to make sure a datefield contains a real date, that all required fields have values, etc.)

如果需要的话，将检查写进proc以确保最终结果是正确的。我是一个ETL专家，在尝试将数据导入表之前，我总是编写procs以使数据得到清理和规范化。如果你做事情从用户界面这可能不是在proc如此重要,尽管我将用户inteface做检查之前运行proc确保有利于插入的数据(比如检查以确保datefield包含一个真正的日期,所有必需的字段有值,等等)。

If you are writing procs to put large amounts of data into tables, it is best to have a way to test those results before they are finalized. You'd be amazed at the junk you will get from clients and vendors for data imports. We write all our import procs with a test flag. That way you can return the select data rather than perform the action, so that you can see in advance, exactly what you would be affecting.

如果您正在编写将大量数据放入表的procs，那么最好有一种方法在这些结果最终确定之前对它们进行测试。您可能会对客户和供应商提供的数据导入垃圾感到惊讶。我们用测试标志编写所有的导入过程。这样，您就可以返回select数据，而不是执行操作，这样您就可以提前看到您将要影响的内容。

I'm not a fan of dynamic SQL and I prefer not to use it in stored procs. If you are stuck with dynamic SQl in existing procs, please put in a debug flag that will allow you to print the SQL rather than execute it. Then put in the comments the most typical cases that you will need to run. You will find that you can maintain the proc much better if you do this.

我不喜欢动态SQL，我不喜欢在存储的procs中使用它。如果您在现有的procs中使用了动态SQl，请输入调试标志，它允许您打印SQl而不是执行它。然后在注释中输入您需要运行的最典型的情况。如果这样做，您将发现您可以更好地维护proc。

Do not do things in a cursor, just becasue you want to reuse another stored proc that only works on one record at time. Code reuse that causes performance issues if a bad thing.

不要在游标中执行操作，因为您希望重用另一个每次只在一条记录上工作的存储proc。如果一件坏事发生，代码重用会导致性能问题。

If you are using case statements or if statements, make sure you have done testing that will hit every possible branch. The one you don't test is the one that will fail.

如果您正在使用case语句或If语句，请确保您已经完成了测试，这将会影响到每个可能的分支。你没有测试的是会失败的。

#8

With SQL Server 2008 use the TRY...CATCH construct, which you can use within your T-SQL stored procedures to provide a more graceful mechanism for exception handling than was available in previous versions of SQL Server by checking @@ERROR (and often the use of GOTO statements) after each SQL statement.

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

         BEGIN TRY
             one_or_more_sql_statements
         END TRY
         BEGIN CATCH
             one_or_more_sql_statements
         END CATCH

When in a CATCH block, you could use the following error functions to capture information about the error that invoked the CATCH block,

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

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

Unlike @@error, which is reset by each statement that is executed, the error information retrieved by the error functions remains constant anywhere within the scope of the CATCH block of a TRY...CATCH statement. These functions could allow to modularize the error handling into a single procedure so you do not have to repeat the error-handling code in every CATCH block.

与@@error不同，它是由执行的每个语句重置的，而error函数检索到的错误信息在TRY的CATCH块范围内的任何地方都保持不变……捕捉语句。这些函数可以将错误处理模块化为一个过程，因此不必在每个CATCH块中重复错误处理代码。

#9

Basic stuff:

基本材料:

Have an error-handling policy, and trap errors on all SQL statements.
Decide on a policy for using source code control for stored procedures.
Include a commented header with user, date/time, and purpose of the sp.
Explicitly return 0 (success) for successful execution, something else otherwise.
For non-trivial procedures, include a test case (or cases) and description of expected result.
Get in the habit of performance-testing. For text cases, record execution time at least.
Understand explicit transactions, and use them.
Almost never call SPs from SPs. Reusability is a different ballgame with SQL.

有一个错误处理策略，并在所有SQL语句中捕获错误。决定为存储过程使用源代码控件的策略。包含带有用户、日期/时间和sp目的的注释头。显式地返回0(成功)，以便成功执行，否则将返回其他内容。对于非平凡的过程，包括一个测试用例(或案例)和预期结果的描述。养成性能测试的习惯。对于文本情况，至少记录执行时间。理解显式事务，并使用它们。几乎从不给SPs打电话。可重用性是一种与SQL不同的游戏。

#1