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 END 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
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
– 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
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
[...] Use BEGIN…END blocks only when multiple statements are present within a conditional code segment. (Read More Here) [...]