Statements after END in stored procedure

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.

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.

Encloses a series of Transact-SQL statements so that a group of Transact-SQL statements can be executed. BEGIN and END are control-of-flow language keywords.

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.

When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN. When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL. If SET ANSI_NULLS is not specified, the setting of the ANSI_NULLS option of the current database applies. For more information about the ANSI_NULLS database option, see ALTER DATABASE (Transact-SQL) and Setting Database Options.

SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

For a script to work as intended, regardless of the ANSI_NULLS database option or the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that might contain null values.

SET ANSI_NULLS should be set to ON for executing distributed queries.

SET ANSI_NULLS must also be ON when you are creating or changing indexes on computed columns or indexed views. If SET ANSI_NULLS is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. SQL Server will return an error that lists all SET options that violate the required values. Also, when you execute a SELECT statement, if SET ANSI_NULLS is OFF, SQL Server will ignore the index values on computed columns or views and resolve the select operation as if there were no such indexes on the tables or views.