SQL SERVER – Correct Syntax for Stored Procedure SP

Just a day ago, I received interesting question about correct syntax for Stored Procedure. Many readers of this blog will think that it is very simple question. The reason this is interesting is the question behavior of BEGIN … END statements and GO command in Stored Procedure.

Let us first see what is correct syntax.

Correct Syntax:
CREATE PROCEDURE usp_SelectRecord
AS
BEGIN
SELECT
*
FROM TABLE
END
GO

I have seen many new developers write statements after END statement. This will not work but will probably execute first fine when stored procedure is created. Rule is anything between BEGIN and END will be created as part of the stored procedure and will work fine. However, anything after GO statement will be not part of Stored Procedure. Nesting of BEGIN … END statement is allowed in Stored Procedure.

Incorrect Syntax : (This may not throw an error)

CREATE PROCEDURE usp_SelectRecord
AS
BEGIN
SELECT
*
FROM TABLE
END
GO
SELECT *
FROM TABLE

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Stored Procedure
Previous Post
SQL SERVER – 2005 – List All Stored Procedure in Database
Next Post
SQL SERVER – Database Interview Questions and Answers Complete List

Related Posts

51 Comments. Leave new

Leave a Reply

Menu
Exit mobile version