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.

Syntax to create a stored procedure


create procedure procedure_Name
(
--declare parameter 
@parameters datatype;
)
AS
BEGIN
--Sql statements;
END

Syntax 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_sample

2. 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 Name

4. 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_sample

6. 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.

Online Members