Stored Procedure

Stored Procedure in SQL Server

Overview

A stored procedure is nothing more than prepared SQL code that you save so you can reuse the code over and over again. So if you think about a query that you write over and over again, instead of having to write that query each time you would save it as a stored procedure and then just call the stored procedure to execute the SQL code that you saved as part of the stored procedure.

In addition to running the same SQL code over and over again you also have the ability to pass parameters to the stored procedure, so depending on what the need is the stored procedure can act accordingly based on the parameter values that were passed.

How to Write a Stored Procedure in SQL Server

First we will create a table called Students whose structure is given below:

CREATE TABLE Students
(
[Studentid] [int] IDENTITY(1,1) NOT NULL,
[Firstname] [nvarchar](200) NOT NULL,
[Lastname] [nvarchar](200) NULL,
[Email] [nvarchar](100) NULL
)

For testing insert the following data into the above table:

Insert into Students (Firstname, lastname, Email) Values('Holland', 'Roden', 'Holland@abc.com')
Insert into Students (Firstname, lastname, Email) Values('Crystal', 'Reed', 'Crystal@abc.com')
Insert into Students (Firstname, lastname, Email) Values('Shelley', 'Hennig', 'Shelley@abc.com')
Insert into Students (Firstname, lastname, Email) Values('Tyler', 'Posey', 'Tyler@abc.comm')

Stored Procedure Syntax

Create Procedure Procedure-name
(
Input parameters ,
Output Parameters (If required)
)
As
Begin
Sql statement used in the stored procedure
End

Now, suppose we need to create a Stored Procedure which will return a student name whose studentid is given as the input parameter to the stored procedure.

Create PROCEDURE Getstudentname(
/* Getstudentname is the name of the stored procedure*/
@studentid INT --Input parameter
)
AS
BEGIN
SELECT Firstname+' '+Lastname FROM Students WHERE studentid=@studentid
END

Execution of the Stored Procedure

Execute Getstudentname 3
or
Exec Getstudentname 3

We can also collect the student name in the output parameter of the Stored Procedure.

Create PROCEDURE GetstudentnameInOutputVariable
(
@studentid INT, --Input parameter
@studentname VARCHAR(200) OUT -- Out parameter to catch studentname
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname FROM Students WHERE studentid=@studentid
END

Execution of the Stored Procedure

Declare @Studentname as nvarchar(200) -- Declaring the variable to catch studentname
Execute GetstudentnameInOutputVariable 2 , @Studentname output
select @Studentname -- "Select" Statement display the output of the Stored Procedure

Alter a Stored Procedure

In SQL Server, a stored procedure can be modified usining the Alter keyword.To get student email address through the same procedure GetstudentnameInOutputVariable.We need to modify it by adding one more output parameter " @StudentEmail ".

Alter PROCEDURE GetstudentnameInOutputVariable
(
@studentid INT, --Input parameter
@studentname VARCHAR(200) OUT , -- Out parameter to catch studentname
@studentemail VARCHAR(200) OUT -- Out parameter to catch Email
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname , @studentemail = Email FROM Students WHERE studentid=@studentid
END

Execution of the Stored Procedure

Declare @Studentname as nvarchar(200) -- Declaring the variable to catch studentname
Declare @studentemail as nvarchar(200) -- Declaring the variable to catch Email
Execute GetstudentnameInOutputVariable 2 , @Studentname output ,@studentemail output
select @Studentname ,@studentemail -- "Select" Statement display the output of the Stored Procedure

DML Statements in Stored Procedures

Stored procedure doesn't have to return anything. Instead a stored procedure used to Insert, delete or update a SQL statement.

Create Procedure InsertStudentrecord
(
@StudentFirstName Varchar(200),
@StudentLastName Varchar(200),
@StudentEmail Varchar(50)
)
As
Begin
Insert into Students (Firstname, lastname, Email)
Values(@StudentFirstName, @StudentLastName,@StudentEmail)
End

Execution of the Stored Procedure

DECLARE @return_value int
EXEC @return_value = [dbo].[InsertStudentrecord]
@StudentFirstName = 'Arden',
@StudentLastName = 'Cho',
@StudentEmail = 'Arden@abc,com'
SELECT 'Return Value' = @return_value
GO

SELECT * FROM Students

DML & DQL Statement in Stored Procedures

Select, Insert, Update, Delete Using Stored Procedure

Create PROCEDURE MasterInsertUpdateDelete
(
@id INTEGER,
@first_name VARCHAR(10),
@last_name VARCHAR(10),
@Email VARCHAR(20),
@StatementType nvarchar(20) = ''
)

AS
BEGIN
IF @StatementType = 'Insert'
BEGIN
insert into Students (Firstname, Lastname, Email) values( @first_name, @last_name,@Email)
END

IF @StatementType = 'Select'
BEGIN
select * from Students
END

IF @StatementType = 'Update'
BEGIN
UPDATE Students SET
Firstname = @first_name, Lastname = @last_name, Email = @Email

WHERE Studentid = @id
END

else IF @StatementType = 'Delete'
BEGIN
DELETE FROM Students WHERE Studentid = @id
END
end

Execution of the Stored Procedure

DECLARE @return_value int
EXEC @return_value = [dbo].[MasterInsertUpdateDelete]
@id = '',
@first_name = 'Tyler',
@last_name = 'Hoechlin',
@Email = 'Tyler@abc.com',
@StatementType = 'Insert'
SELECT 'Return Value' = @return_value
GO