存储过程中 END 之后的语句 [英] Statements after END in stored procedure

问题描述

我今天遇到了一个有趣的问题.我正在更改一个存储过程并在最后放置一个 select 语句.它是临时的，仅用于处理数据.后来我惊讶地发现该语句被保存并在 SP 运行时执行.

设置 ANSI_NULLS ON走-- 评论通常放在此处并保存为 SP 的一部分更改程序 [dbo].[MySP]@param INT作为开始--这里是你正常的SQL语句结尾--这里也可以添加SQL语句从大表中选择 *--您可以访问参数选择@param

保存所有内容是有道理的，而不仅仅是BEGIN/END里面的内容，否则注释和SET ANSI_NULLS等都会消失.我对从哪里开始有点困惑，所以我有几个问题:

1. SET ANSI_NULLS 被保存为 SP 的一部分.我已经确认每个SP都有自己的价值.SQL Server 如何知道将它保存为 SP 的一部分，因为它以前没有被引用过?它是否对当前环境状态进行全面扫描，然后在 ALTER PROCEDURE 运行时保存状态(可能只有非默认值)?
2. 显然 BEGIN/END 是可选的，没有内在意义.为什么他们甚至包括在内?它们给人一种不存在的范围的错误感觉.在我看来，没有 BEGIN/END，最后一个 GO 最有意义.

解决方案

ANSI NULLS 和 QUOTED IDENTIFIERS 被存储为存储过程代码的元数据属性.您可以通过

查看这些设置

select * from sys.sql_modules

保存过程时，这些属性将设置为用于保存过程的连接的任何属性.这可能会导致恼人的不一致，所以要小心.

至于 BEGIN/END，正如@bobs 所说的那样——它们表示代码块，它们不表示存储过程代码的开始和结束.(函数，是的，过程，不是.)正如你所说，没有 BEGIN/END 和最后一个 GO 最有意义 是我多年来一直在做的方式.>

从技术上讲，SQL 将(尝试)批量保存所有内容作为存储过程的一部分——也就是说，您提交的所有文本，由 GO 语句(如果有)分解.如果您在临时查询之前插入 RETURN 语句，它们将包含在代码中但永远不会运行.

I came across an interesting problem today. I was altering a stored procedure and put a select statement at the very end. It was meant to be temporary and just for working with the data. I was surprised to find out later that the statement got saved and was executing whenever the SP ran.

SET ANSI_NULLS ON
GO

-- Comments usually go here and are saved as part of the SP
ALTER PROCEDURE [dbo].[MySP]
    @param INT
AS
BEGIN
    --Your normal SQL statements here
END

--You can also add SQL statements here
select * from LargeTable

--You have access to the params
select @param

It makes sense that everything is saved, not just what is inside BEGIN/END, otherwise the comments and SET ANSI_NULLS, etc. would disappear. I'm a little confused with what starts where, so I have a few questions:

1. SET ANSI_NULLS gets saved as part of the SP. I have confirmed that each SP has its own value. How does SQL Server know to save this as part of the SP since it's not referenced before? Does it do a full scan of the current environment state, then when ALTER PROCEDURE runs it saves the state (possibly only non-default values)?
2. Apparently the BEGIN/END are optional and have no intrinsic meaning. Why are they even included then? They give a false sense of scope that doesn't exist. It seems to me no BEGIN/END and a GO at the end would make the most sense.

解决方案

ANSI NULLS and QUOTED IDENTIFIERS are stored as metadata attributes of the stored procedure code. You can review these settings via

select * from sys.sql_modules 

When a procedure is saved, these attributes are set to whatever they are for the connection through which the procedure is being saved. This can lead to irritating inconsistancies, so be wary.

As for BEGIN/END, it's exactly as @bobs says -- they denote code blocks, they do not denote the start and end of stored procedure code. (Functions, yes, procedures, no.) As you say, no BEGIN/END and a GO at the end would make the most sense is the way I've been doing it for years.

Technically, SQL will (attempt to) save everything in a batch as part of the stored procedure -- that is, all the text you submit, as broken up by GO statements (if any). If you stuck a RETURN statement right before your ad hoc queries, they'd be included in the code but never run.

这篇关于存储过程中 END 之后的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！