是“SET NOCOUNT ON"吗?占位符存储过程体的好选择? [英] Is "SET NOCOUNT ON" a good choice for a placeholder stored procedure body?

问题描述

这些答案(1、2)，使用来自 这篇文章，推荐使用语句SET NOCOUNT ON作为其主体将很快被覆盖的存储过程的临时占位符/虚拟主体.

These answers (1, 2), using code from this article, recommend using the statement SET NOCOUNT ON as a temporary placeholder/dummy body for a stored procedure whose body will be overwritten shortly.

这些答案和那篇文章都没有说明为什么 SET NOCOUNT ON 被选为临时存储过程主体的值.

Neither those answers nor that article specify why SET NOCOUNT ON was chosen as the value for the temporary stored procedure body.

我的问题:为什么 SET NOCOUNT ON 是临时存储过程体的好选择，它会(如果一切顺利)在后续操作中被覆盖?有没有更好的选择?

My question: Why is SET NOCOUNT ON a good choice for a temporary stored procedure body which will (if all goes well) be overwritten in a subsequent operation? Is there a better alternative?

想到什么可能构成一个好的临时/占位符存储过程体的一些标准:

Some criteria that come to mind for what might constitute a good temporary / placeholder stored procedure body:

• 如果由于某种原因存储过程的后续 ALTER 没有按计划发生，则在运行时会以明显的方式失败；
• 将来维护存储过程的开发人员很容易理解；
• 不会增加任何显着的开销.

• Fails in an obvious way at runtime if for some reason the subsequent ALTER of the stored procedure doesn't happen as planned;
• Is easy to understand by future developers maintaining the stored procedure;
• Doesn't add any significant overhead.

推荐答案

为了更好地满足我的问题中的标准"，我将 SET NOCOUNT ON 替换为 RAISERROR声明.

To better meet the "criteria" from my question, I've landed on replacing SET NOCOUNT ON with a RAISERROR statement.

在对存储过程运行 ALTER PROCEDURE 之前确保存储过程存在的代码最终看起来像:

My code to ensure a stored procedure exists before running an ALTER PROCEDURE on it ends up looking like:

-- Create the sproc with a temporary body if it doesn't exist yet. 
-- We'll set the real body in the ALTER PROCEDURE statement below.
IF NOT EXISTS (
  SELECT * FROM sys.objects
    WHERE name = 'MyStoredProcedureNameHere'
    AND type = 'P'
) BEGIN
  EXEC ('CREATE PROCEDURE MyStoredProcedureNameHere AS
    RAISERROR (''The ALTER PROCEDURE to set the body for MyStoredProcedureNameHere did not run as it should have!'', 16, 1);');
END
GO

ALTER PROCEDURE MyStoredProcedureNameHere AS ...

因此，如果 ALTER PROCEDURE 以某种方式无法运行，那么如果我的存储过程被执行，它将引发错误而不是静默地做任何事情(就像仅由 SET NOCOUNT 组成的主体的情况一样开启).

So, if the ALTER PROCEDURE somehow fails to run, then if my stored procedure gets executed, it'll raise an error instead of silently doing nothing (as would be the case with a body consisting only of SET NOCOUNT ON).

此方法的功劳:http://www.codeofhonor.com/blog/a-better-way-to-update-sql-stored-procedures

这篇关于是“SET NOCOUNT ON"吗?占位符存储过程体的好选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！