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
— 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
create procedure sri(uname1 varchar(30),pwd1 varchar(20),f out int)
as
begin
select count(*) into f from login where uname=uname1 and pwd=pwd1;
end
My above reply should be
create procedure sri(uname1 varchar(30),pwd1 varchar(20),f out int)
as
begin
select count(*) as f from login where uname=uname1 and pwd=pwd1;
end
“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
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
You can use sp_depnds for this easily
EXEC sp_depnds 'your_procedure_name'
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
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.
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
No impact. It is valid
how to create a store procedure to inner join
Can you be more specific on what you are trying to do?
how to creat database and stored procedure in single schema
Regards
Arul
Login to that specific user and create the objects
Please help me sir….
i want know (TINY) in sql..
please reply me with Syntax
There is a datatype called tinyint in SQL Server whcih stores values between 0 and 255
i want to basic knowledge Sql server….
please help me sir
Search for “Learn SQL” in google/bing
kya bath hai
???
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
Create database test
GO
Use test
GO
create table testing(i int)
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?
It is only for an example. You can use any number of statements inside BEGIN and END
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.
It is optional. If you use IF statement it is better practice to use BEGIN and END
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.
Post some sample data with expected result
what r the basic difference between function and procedure
i want to create a stored procedure and function in sql any one pls help me.. give example and syntax
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
I want to basic concept of sql stored procedures and code with example
Have a look at them in BOL, a SQL Server help file It has example codes as well as detailed information about them
How to execute Stored procedure in sql sever
Exec