Statements after END in stored procedure

Question

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.

Answer 1

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.

Answer 2

The BEGIN...END defines a block of code. It doesn't define the beginning and ending of a script or procedure. But, I agree it can be confusing.

The SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are saved but not the other settings. Check out Interoperability here for more information.