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)
51 Comments. Leave new
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)
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?
I need the exact syntax explanation for the stored proccedure..?
Could you help me out on this.
i want to know how to clear the expression of non boolian type specified context where condition is expected near begin?
Thanks a lot
It is he most helpful pos I have ever seen…. Thanx to u all who is makin it easy for begginers to understand sql
i want to understand concept of stored procedure for sql and what is difference for stored procedure in sql vs oracle
thanks
give me a proper definition of store procedure please replay?
I am totally beginner to PL /SQl, and learning Procedures, but can identify when to use IS and when AS, (or both are same) in following syntax-
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, …])]
{IS | AS}
BEGIN
END procedure_name;
Plz help. Thank you.
PL/SQL is Oracle’s language.
hello Pinal,
how to see script of table through query not with wizard (not like generate script of table)
Script can be generated via Wizard. You can look at table details by using sp_help