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 (http://blog.SQLAuthority.com)

About these ads

47 thoughts on “SQL SERVER – Correct Syntax for Stored Procedure SP

  1. – Please take a look at the below code there is a statement
    — after the Nested BEGIN….END, which will be executed
    — every time the SP is called

    Create procedure New_test
    as
    begin
    print ‘hai’
    begin
    print ‘hello’
    end
    end — Actual end of Nested BEGIN & END
    print ‘hello_123′ — Out of scope, But executes

  2. in oracle stored procedureis
    create procedure sri(uname1 in varchar,pwd1 in varchar,f out number)
    is
    begin
    select count(*) into f from login where uname=uname1 and pwd=pwd1;
    end
    plz write this into sqlserver2005

  3. Pingback: SQL SERVER - Guidelines and Coding Standards Part - 1 Journey to SQL Authority with Pinal Dave

  4. “This will not work but will probably execute first fine when stored procedure is created.”

    This is not true. I just tested on SQL 2008 and even the code outside the END executes with the proc.

    The relevant command is the GO command, it’s what executes the CREATE STATEMENT.

    -Cole

  5. Hi This is Rahul

    As you have given some query for to fine out the stored procedure which are related to one or another table.

    —-Option 1
    SELECT DISTINCT so.name
    FROM syscomments sc
    INNER JOIN sysobjects so ON sc.id=so.id
    WHERE sc.TEXT LIKE ‘%tablename%’

    I have checked the above query with two different input,but i am getting two output.
    First i have given the table name as dbo.tablename,
    i am getting lot of stored prcdure output
    second time i have given only the table name as “table name” only, that time i am getting different outout.
    Can you please explain, y this happened?

    thanks

  6. Pinal,

    Begin and End can be used if you want to execute set of commands if some conditions are met (using a if statement)
    Otherwsie thery are optional and all statements will work even there are statements after END

  7. What is going on here?

    You have two examples, one of which is supposed to have the correct syntax and the other is supposed to have incorrect syntax.

    But both examples are *identical*. This blog entry is confusing.

  8. Hi,

    Can we add sql select statements between ‘AS’ and ‘BEGIN’? like ..

    AS
    DECLARE @sDate varchar(20),
    @sNm varchar(40),
    @sid int

    SELECT @sid= s_id
    FROM dbo.su
    WHERE s_cd= @userid

    BEGIN

    Any impact?

    – JB

  9. Hello sir..
    I am working with SQL Server ,,
    I don’t know…
    Create a Data base.
    Create a table,
    Please help me…
    i am wait

  10. In your Guideliness of SQL Server , you have mention that “Use BEGIN .. END blocks only when multiple statementa are present within a conditional code segment.”
    But the example you have given here contains only one statement where you have used BEGIN END block?

  11. Is there any sence in writing begin at start and end at end of SP??

    Is it working like transactions??

    Please clarify me the use of begin and end.

  12. Hi, I’m new to stored procedures. I can’t seem to find resources on examples of Stored Procedures.

    How can I write one to select a field from a table, do some calculations using that field, and then update the value of another field in the same table with the result of the calculation. In the select statement I need to select all the records so I’m guessing there is a loop for the second part.

    • Create procedure proc_name
      as
      –procedure code here

      GO

      Create function func_name
      returns datatype
      as
      –procedure code here

      return
      GO

      SQL Server help file will help you understand better

  13. Query to find duplicate tables in the database

    Select * from sys.tables where name in (Select name from sys.tables group by name having COUNT (*) = 1)

  14. which is correct to make changes in stored procedure or to drop the created procedure and create a new one?
    If so what is the syntax to alter the stored procedure?

  15. Pingback: SQL SERVER – Weekly Series – Memory Lane – #005 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s