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
- System Stored Procedures
- Extended Procedure
- CLR Stored Procedure
- 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
System Stored Procedure | Description |
---|---|
sp_rename | It is used to rename a database object like stored procedure, views, table etc. |
sp_changeowner | It is used to change the owner of a database object. |
sp_help | It provides details on any database object. |
sp_helpdb | It provides the details of the databases defined in the SQL Server. |
sp_helptext | It provides the text of a stored procedure reside in SQL Server |
sp_depends | It provides the details of all database objects that depend on the specific database object. |
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
patient_id | name | age | gender | address | disease | doctor_id |
---|---|---|---|---|---|---|
1 | reema | 23 | female | althan,Surat | fever | 21 |
2 | kusum | 50 | female | vadodara | heart failure | 22 |
3 | carlin | 43 | male | vapi | infection | 23 |
4 | rahul | 26 | male | navsari | cancer | 21 |
6 | hansha | 55 | female | vapi | diabetes | 22 |
Doctor Table
doctor_id | name | age | gender | address |
---|---|---|---|---|
21 | asif | 55 | male | baruch |
22 | dhawal | 40 | male | Surat |
23 | krishna | 39 | female | Surat |
24 | lissa | 35 | female | Navsari |
25 | leeba | 34 | female | baruch |
26 | vini | 33 | female | Surat |
27 | Dhiren | 32 | male | Navsari |
Bill Table
bill_no | patient_id | doctor_id | room_charge | no_of_days |
---|---|---|---|---|
5005 | 1 | 340 | 500 | 4 |
5006 | 2 | 600 | 480 | 8 |
5008 | 3 | 800 | 340 | 3 |
5009 | 4 | 780 | 890 | 6 |
5010 | 3 | 400 | 1 | |
5011 | 1 | 200 | 300 | 1 |
5012 | 2 | 600 | 110 | 2 |
5013 | 3 | 330 | 210 | 1 |
5014 | 1 | 230 | 340 | 2 |
Laboratory Table
lan_no | patient_id | doctor_id | date | amount |
---|---|---|---|---|
10 | 1 | 21 | 02-02-2000 | 4000 |
20 | 2 | 21 | 09-09-2001 | 300 |
30 | 3 | 22 | 03-03-2001 | 600 |
40 | 1 | 23 | 02-06-2002 | 800 |
50 | 4 | 21 | 05-07-2003 | 900 |
60 | 2 | 25 | 10-04-2004 | 550 |
70 | 4 | 22 | 03-04-2005 | 900 |
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