How to write a stored procedure in sql server
A Stored procedure is nothing but a group of sql statement that performs a specific task under single Execution plan. If you want to do a same task again and again than we can create a Stored procedure.Once we create a procedure then we can reuse the code by Execute Statement.
A Stored procedure is nothing but a group of sql statement that performs a specific task under single Execution plan. If you want to do a same task again and again than we can create a Stored procedure.Once we create a procedure then we can reuse the code by Execute Statement.Syntax to create a stored procedure
create procedure procedure_Name
(
--declare parameter
@parameters datatype;
)
AS
BEGIN
--Sql statements;
ENDSyntax to execute a stored procedure
Exec procedure_Name 'value_for_parameter'
Create a table tbl_sample with following fields and values:
create table tbl_Sample(sno int identity,Name varchar(10),Department varchar(10))
insert into tbl_Sample values
('Ram','ECE'),
('Suresh','IT'),
('Vijay','CSE'),
('Ram','CSE'),
('Kumar','IT')1. To create a simple stored procedure
For select all the values from tbl_sample we can create a simple procedure.
create procedure Sp_sample
AS
BEGIN
select * from tbl_Sample
END
--To execute the procedure use this Execute statement
Exec Sp_sample2. Procedure with parameters
If we want details about any particular department we can use parameters in stored procedure to get the specific department which we mentioned in parameter. Depends on the value of Parameter where condition will change.
Here I'm using Alter statement to modify the procedure Sp_sample.
Alter procedure Sp_sample
(
@department varchar(10)
)
AS
BEGIN
select * from tbl_Sample where department=@department
END
--To execute the procedure use this Execute statement
--Exec Sp_sample 'CSE'3. Procedure with Output parameters
In stored procedure we can declare parameter as Output parameter which is reused as input in some other stored procedures.
We have to declare the output parameter while executing.
Alter procedure Sp_sample
(
@sno varchar(10),
@name varchar(10) output
)
AS
BEGIN
select @name=name from tbl_Sample where sno=@sno
END
--To execute the procedure use these Execute statement
Declare @name varchar(10)
Exec Sp_sample 5,@name output
select @name Name4. Procedure for Insert, Update & Delete
Stored procedures not only used to select the data. We can use DDL and DML commands in stored procedures.
create procedure sp_DML
(
@name varchar(10),
@department varchar(10))
AS
BEGIN
insert into tbl_sample (name,department)values(@name,@department)
update tbl_sample set department='Null' where name like 'Store%'
Delete table tbl_sample where department='Null'
select * from tbl_sample
END
--To execute the procedure use this Execute statement
Exec sp_DML 'Priya','EEE'5. Performance boost for Stored procedures
Normally executing a procedure it returns number of rows affected for each insert, alter and update statements. To increase a performance of a stored procedure we have to add a statement SET NOCOUNT ON in our procedure.
It will greatly reduce the network traffic in database. SET NOCOUNT ON will eliminate the row affected statements and makes our procedure more efficient.
By using:
select @@ROWCOUNT
We can get latest affected rows count.
SET NOCOUNT OFF is used to returns number of rows affected for each insert, alter and update statements.
SET NOCOUNT ON
GO
create procedure sp_update
(
@department varchar(10)
)
AS
BEGIN
update tbl_sample set department=@department where department='CSE'
END
--To execute the procedure use this Execute statement
Exec sp_update 'CS'
--Check the table
select * from tbl_sample6. To delete a procedure
If we don't want the procedures just delete the procedure using drop command.drop procedure sp_update
7. Structure of stored procedure
For our convenience we can add some new features to our stored procedure. For every time we have to recreate our procedure. For that we have to add a simple command to our procedure.
This command will drop the procedure if it already exists.
If OBJECT_ID('sp_insert') is not null
Drop procedure sp_insert
SET NOCOUNT ON
GO
If OBJECT_ID('sp_insert') is not null
Drop procedure sp_insert
GO
create procedure sp_insert
(
@name varchar(10),
@department varchar(10))
AS
BEGIN
Insert into tbl_sample (name,department)values(@name,@department)
SET NOCOUNT OFF
END
--To execute the procedure use this Execute statement
Exec sp_insert 'Sona','CS'
Main Advantages of Store Procedure:
Group of SQL statement can be compiled into a single Execution plan.
Reusability.
I didn't find any enhancement made to the Sql Server 2008 procedure when compared to earlier versions of Sql Server
Stored procedure is explored well but how it boosts the preformance is not well explained and also the definition of the Stored procedure is not prefect upto the feature of the Procedure.
On the whole Stored Procedure is neatly explained with examples. I have simplified a stored procedure from 2976 lines of code to approximately 600 lines of code.