CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
krystan honour
krystan honour

Reputation: 6793

Why is this statement included in the stored procedure definition?

I got nailed today by a typo, its a simple typo when I defined a script to create a stored procedure under Sql Server 2005, I forgot a GO statement in between the END statement and the GRANT statement, this lead to the GRANT statement being included in the stored procedure definition and running at the end of the stored procedure (verified with SQL Profiler).

Here is the code:

USE [TestGround]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[FooProc]
AS 
    BEGIN

    SELECT * FROM dbo.Foo
    END

-- a GO statement is missing here..   
GRANT EXECUTE ON [dbo].[Foo] TO dbo

I understand that GO signifies the end of a batch, but I was surprised to see that the GRANT statement was included in the stored procedure, I've never forgotten the GO statement before so haven't seen this issue.

Could someone please explain to me why this happens?

Upvotes: 0

Views: 43

Answers (1)

Hans Kesting
Hans Kesting

Reputation: 39283

You can leave out the BEGIN / END pair: the definition doesn't end at END. It continues until the end of the file or a GO, whichever comes first.

Upvotes: 1

Related Questions