CODEWITHSUNDEEP
sql-serverstored-procedures
daremkd
daremkd

Reputation: 8424

Ending a stored procedure in .sql file

I have this in an SQL file and I assumed that end is going to end the stored procedure:

create procedure Test4
as
begin
  select * from People
end
select * from Region

But instead, I got "Command completed successfully" and when I looked at the stored definition, I got:

ALTER procedure [dbo].[Test4]
as
begin
  select * from People
end
select * from Region

So what's the use of BEGIN/END in a stored procedure in SQL Server if it isn't to actually END it?

Upvotes: 2

Views: 994

Answers (1)

Jonathan Michalik
Jonathan Michalik

Reputation: 1532

According to the MSDN post on CREATE PROCEDURE, the BEGIN-END block is optional:

{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] } One or more Transact-SQL statements comprising the body of the procedure. You can use the optional BEGIN and END keywords to enclose the statements. For information, see the Best Practices, General Remarks, and Limitations and Restrictions sections that follow.

I just ran a test against MS SQL Server and noticed that if I include a query before the GO keyword after the END it gets included in the procedure: 

CREATE PROCEDURE [dbo].[Test]

AS
BEGIN
    SELECT TOP 100 * FROM Test1
END
-- This gets included in the procedure
SELECT TOP 100 * FROM Test2
GO

Whereas if I type it after the GO, the query is left out of the procedure:

CREATE PROCEDURE [dbo].[Test]

AS
BEGIN
    SELECT TOP 100 * FROM Test1
END
GO
-- This gets chopped off when I open the procedure for editing later
SELECT TOP 100 * FROM Test2

Upvotes: 4

Related Questions