Stored Procedure

Draft , SQL Tricks ,

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.

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

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

 DML Statements in Stored Procedures 

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, Insert, Update, Delete Using Stored Procedure