SQL Stored Procedure Explained [Practical Examples]

Overview of SQL Stored Procedure

SQL stored procedures are generally precompiled group of an ordered series of Transact-SQL statements stored into a single logical unit which allow for variables and parameters declaration as well as selection and looping constructs, Based on the statements in the procedure and the parameters you pass, it can perform one or multiple DML operations on the database, and return value if any. Thus, it allows you to pass the same statements multiple times, thereby, enabling reusability

A key point is that stored procedures are stored in the database rather than in a separate file.

Benefits of SQL Stored Procedure

**1. Reduced server/client network traffic:**SQL stored produce contains all executable SQL commands in a single unit to be executed so it will significantly reduce network traffic between the server and client because only the call to execute the procedure is sent across the network

2. Reuse of code: The SQL statements which are encapsulated into stored procedures can be repeatedly used for the same database operations by multiple users

**3. Easier maintenance:**When we use stored producer to make changes to the database with client applications only the procedures must be updated for any changes in the underlying database, we do not need to make any changes to client application. The application tier remains separate and does not have to know how about any changes to database layouts, relationships, or processes.

**4. Stronger security:**The procedure controls what processes and activities are performed and protects the underlying database objects. This eliminates the requirement to grant permissions at the individual object level and simplifies the security layers

**5. Improved performance:**When SQL Stored Procedure compiles for the first time it is executed and creates an execution object or plan that is reused for subsequent executions, so it will be improved the performance of the application

Types of Stored Procedures

1. System Stored Procedures
2. Extended Procedure
3. CLR Stored Procedure
4. User-defined Stored Procedures

1. System Stored Procedures

SQL Server has some already defined hidden store procedures which logically appear in the sys database of each user-defined and system-defined database, these SQL System Stored Procedure starts with the sp_ prefix so we cannot use such type of prefix in user-defined stored procedures

List of System-Defined Stored Procedure

2. Extended Procedure

Extended procedures provide an interface to external programs for various maintenance activities. These extended procedures start with the xp_ prefix and are stored in the Master database. Basically, these are used to call programs that reside on the server automatically from a stored procedure or a trigger run by the server.

3. CLR Stored Procedures

SQL Server contains a special type of stored procedures which are based on CLR (Common Language Runtime) in .net Framework, CLR integration of procedure was introduced with SQL Server 2008 and allows for the procedure to be coded in one of .NET languages like C#, Visual Basic and F#. I will discuss the CLR stored procedure later

4. User-defined Stored Procedures

These procedures use DML and DDL commands together for selecting, updating, or removing data from database tables. A stored procedure specified by the user accepts input parameters and returns output parameters

Syntax to create SQL Stored Procedure

CREATE PROCEDURE [database_name].procedure_name  
                @parameter_name data_type [ = default_value] [OUTPUT],   
                ....   
                @parameter_name data_type  [ = default_value] [OUTPUT]
AS  
      -- [SET NOCOUNT ON ]
      -- SQL statements  
      -- SELECT, INSERT, UPDATE, or DELETE statement  
RETURN  

Here,

• **CREATE PROCEDURE:**CREATE PROCEDURE is the keywords used to create a stored procedure
• **[database_name].procedure_name:**Specify the user-defined name of the Stored Procedure with the database name in which Stored Procedure is going to be created
• @Parameter_name: Specify the name of parameters to be passed as an argument to the procedure, the name must begin with the ‘@’ sign
• Datatype:Specify any valid SQL datatype
• **[OUTPUT]:**Optional, used to declare parameters as the OUTPUT parameter which wi
• **SQL Statements:**Specify SQL DML (Data Manipulation Language) statements such as insert, update, delete and select statements operate on tables of the specified database in which stored procedure is created
• **RETURN:**Return is the keyword specified to return the result of the Stored procedure

Syntax to Execute a SQL Stored Procedure

EXEC procedure_name;

SQL Stored Procedure Parameters

• A stored procedure can have zero or more INPUT and OUTPUT parameters.
• Each parameter is defined with a name, and datatype as the direction like Input, Output, and Return, if a direction is not specified, then by default, it is Input.
• We can specify a default value for the parameters at the time of declaration using the assignment operator ‘=’
• OUTPUT Parameter is used to return a value to the calling program
• The parameter values must be a constant or a variable. It cannot be a function name

Examples of SQL Stored Procedure

Consider a hospital management database with four tables’ patient, doctor, laboratory, bill for performing practical examples

Patient Table

Doctor Table

Bill Table

Laboratory Table

Example-1: Create SQL Stored Procedure with no parameters

Write SQL stored procedure to retrieve all patient records

CREATE PROCEDURE dbo.patient_data
AS
    select * from patient
RETURN

In the above SQL stored procedure, we have used the SQL select statement to fetch all records from patient table

OUTPUT:

To execute above created stored procedure patient_data

EXEC patient_data

Example-2: Create SQL stored Procedure with one parameter

Write SQL store procedure to display female patient data

create PROCEDURE dbo.Femalepatient
    (
    @gender varchar(20)
    )
AS
    select * from patient where gender=@gender
RETURN

• In the above SQL store procedure, Create procedure statement is used to create procedure Femalepatient with one parameter @gender
• SQL select statement is specified in the above-stored procedure to retrieve data from the patient table based on the condition

OUTPUT:

To execute above created stored procedure Femalepatient

EXEC Femalepatient @gender='Female'

Example-3: Create SQL Stored Procedure with multiple parameters

Write SQL Stored procedure to insert a new record in patient table

CREATE PROCEDURE dbo.add_patient
(
    @patient_id int,
     @name  varchar(20),
     @age int,
     @gender varchar(10),
     @address varchar(20),
     @disease varchar(20),
     @doctor_id int
)

AS  
SET IDENTITY_INSERT patient ON
insert into patient(patient_id,name,age,gender,address,disease,doctor_id) values(@patient_id,@name,@age,@gender,@address,@disease,@doctor_id)
RETURN

• In the above SQL stored procedure, we have specified seven parameters @patient_id,@name,@age,@gender,@address,@disease, and @doctor_id which values need to be passed while executing the procedure
• SQL Insert statement has been specified in the SQL statement section to add new records with specified parameter values
• SET IDENTITY_INSERT ON is applied in the above Stored Procedure to allow explicit values to be inserted into the identity column of a patient table

OUTPUT:

To execute above created stored procedure add_patient

EXEC add_patient @patient_id=8,@name='rekha',@age=56,@gender='Female',@address='Surat',@disease='Cancer',@doctor_id=23

In the above execute store procedure query we need to pass value of each parameters with @parametersname

Example-4: Create SQL Stored Procedure with default parameter values

We can set the default value of any particular parameter or parameters at the time of creating the SQL Store Procedure, so when we execute this procedure we don’t need to pass the value for that parameter SQL will automatically pass a default value

If we specify the value for the default set parameter, SQL will replace the default value with the passing value

Write SQL Stored procedure to retrieve patient information with lab report data with default patient id as 1

CREATE PROCEDURE dbo.default_patientid_data
(
    @patient_id int = 1
)
AS
     select patient.patient_id,name,address  disease,lab_no,date as 'Report date' from patient left join laboratory on patient.patient_id=laboratory.patient_id where patient.patient_id=@patient_id
RETURN

• In the above SQL Store procedure, we have specified one parameter @patient_id with default value as 1
• in the SQL statement section, SQL Select statement is specified to fetch join records from two tables using left outer join with where condition which compares with the value of @patient_id parameter value

OUTPUT:

To execute above created stored procedure add_patient

EXEC default_patientid_data

If we don’t specify the value of the parameter @patient_id default value 1 will be pass

If We explicitly specify the new value for parameter @patient_id it will replace the default value

EXEC default_patientid_data @patient_id=2

Example-5: Create SQL Stored Procedure with OUTPUT parameter

The OUTPUT parameter is used when we want to return some value from the stored procedure

Write SQL Stored Procedure to retrieve total laboratory bill amount of patient whose patient_id pass as parameter

CREATE PROCEDURE dbo.Total_lab_amount_patient_id
       (
    @patient_id int,
    @amount int OUTPUT
    )

AS
select @amount=sum(amount)  from laboratory where patient_id=@patient_id
RETURN

• In the above-stored procedure, we have declared an OUTPUT parameter @amount to return value when executing the stored procedure
• In the SQL Statement section, we have used SQL Select statement with OUTPUT parameter in the selection list so the resulting value of total lab amount will be stored in @amount parameter, also define where condition to pass patient_id as a parameter at the time of execution of the stored procedure

OUTPUT:

To execute above created stored procedure Total_lab_amount_patient_id

DECLARE @amount int
EXEC  Total_lab_amount_patient_id 2 , @amount OUTPUT 
select @amount as 'Total Lab Bill Amount'

• Declare keyword is used to declare a variable to store the value returned from the above-created store procedure
• while executing the above created stored procedure we need to pass the value of patient_id as well as need to specify the output parameter name
• To display the value of the @amount variable SQL select statement is used

Summary

In this article of SQL Store Procedure, We have discussed an overview of SQL Stored Procedure with benefits of using stored procedure, also explain different types of stored procedure , what are the different type of parameters we can use in stored procedure , after that we have explain User-defined stored procedure in details with syntax and practical examples like stored procedure with no parameter, SQL stored procedure with single parameter to multiple parameter, stored procedure with default parameter and OUTPUT parameter

Read More

SQL Stored Procedure