What is a Stored Procedure (SP)
The stored procedure is a set of PL/Sql statements written inside a named block. We use stored procedures in Sql Server to perform the very basic operations on database tables.
Advantages
Stored Procedures are very much useful in multidevelopment environment. As they serve the developers or applications from single point.
They are precompiled and the execution of the stored procedures is very fast when compared to sql queries
They sit inside the database and executes from it. The changes made in one place are visible to everybody whoever is accessing the stored procedures.
Stored Procedure Syntax
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
The syntax is very clear starts with the command CREATE PROCEDURE followed by procedure name and the list of parameters that can be passed to it with datatypes and default values. The parameters declared here must be passed from the context from where the procedure is getting executed. If nothing passed for a parameter and any default value assigned to it then the default value will be taken and the procedure executes. If no default value assigned then it will through an error like 'Value does not supplied for <Parameter_1>'
After the parameter declaration we will begin the actual body of the procedure and start writing the business logic and Sql queries for the processing.
Stored Procedures Best Practices
So far we saw the syntax and parameter declaration for the stored procedure. Now this is the time to see how we can write stored procedures for a particular table. What are the best practices to do it.
For any database table the primary thing or primary operations that we can implement are CRUD operations.
C - Create / Insert
R - Read / Select
U - Update / Edit
D - Delete / Remove
These are the basic operations, that every database programmer should implement for every table. The industry standards or the best practices followed by developers are as follows..
Before getting into the practices first create a table with the following statement on you database.
Create Table Script
CREATE TABLE [dbo].[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [varchar](50) NULL,
[YTDOrders] [int] NULL,
[YTDSales] [int] NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
The table is created as CustomerID as integer, identity column and primary key. So what ever operations we perform the primary key is important to perform different kinds of operations like update, select and delete.
Stored Procedure for UPSERT
Don't get frightened by this new word UPSERT, this is name used by DB programmers for stored procedures which performs both insert + update. So the very first step we should consider for our table is to write a procedure which performs both insert and update operations as below.
Procedure Script
CREATE PROCEDURE Customer_Upsert
@CustomerID int=null,
@CustomerName nvarchar(40)=null,
@YTDOrders int=null,
@YTDSales int=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if(@CustomerID is null)
INSERT INTO Customer(CustomerName,YTDOrders, YTDSales) Values(@CustomerName,@YTDOrders,@YTDSales)
else
UPDATE Customer SET
CustomerName=coalesce(@CustomerName, CustomerName),
YTDOrders=coalesce(@YTDOrders,YTDOrders),
YTDSales=coalesce(@YTDSales,YTDSales)
WHERE CustomerID=@CustomerID
END
GO
Here we are passing all the columns values as parameters and we are assigning them with their respective default values. The advantage of assigning default values is when application developers working with this procedure and they forgot to pass few parameter values then it should break. And the default value assignment is very much useful when we are performing some search kind of filtering and on update operations.
Here we are checking whether the @CustomerID is passed or not? for new records as they wont have any primary column value it will be passed empty and our stored procedure will identifies it as new records and the insertion statement executes.
Try with this sample statement:
EXEC Customer_Upsert @CustomerName='Insert SP',@YTDOrders='13',@YTDSales='4'
And now execute the select statement to see the records in the table
Result after Upsert Stored Procedure Performed Insert
If the @CustomerID is passed our Stored Procedure will identify as it is already exists and executes the Update statement accordingly.
Here the interesting point we should see is COALESCE, this is function is very very useful while updating. Suppose consider in some scenario I have updated only few column values for a customer. I want to update the sales of a customer then I will pass only the new value for sales what about other column values if I dont pass their values if I do not use COALESCE i will end up in some permutations and combinations as I need to write conditions like if only customer name passed or customer name plus sales like this. Now it COALESCE solves my problem and provide simple single line statement to finish my job.
What it will do is. It checks whether the passed value is null or not. If it is null it will assign the old value to it
other wise the new value.
Try with this simple Sql Statement
EXEC Customer_Upsert @CustomerID=1,@YTDSales='10'
Result after upsert stored procedure executes update
Now see the result and compare with the old result select after insert.
This is how we one should write Upsert stored procedure, which will provide two kinds functionality to insert and update.
Stored Procedure For Delete
This is very simple procedure to do, and requires only primary key or any combination of columns. But the delete is of 2 kinds.
Procedure Script
USE [SampleDAC]
GO
/****** Object: StoredProcedure [dbo].[Customer_Delete] Script Date: 01/08/2013 23:28:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Customer_Delete]
@CustomerID int,
@DeletePermanent bit
AS
BEGIN
SET NOCOUNT ON;
IF @DeletePermanent = 1
DELETE FROM Customer WHERE CustomerID=@CustomerID
ELSE
UPDATE Customer SET IsActive=0 where CustomerID=@CustomerID
END
GO
1.Permanent Delete: Here we delete entire row for a passed primary key. The above stored procedure has the section IF to deal with this functionality
IF @DeletePermanent = 1
DELETE FROM Customer WHERE CustomerID=@CustomerID
Example:
EXEC dbo.Customer_Delete 3,1
SELECT * FROM Customer
1 Insert SP 0 10 1
2 update sp 20 23 0
4 select sp 20 23 1
5 select sp1 21 24 1
This shows the results it deleted the records number 3 permanently from the database
2. Soft Delete: Here we will make a row as disabled by means of maintaining a bit column for that table. If the bit value is 1 it is active other wise it is disabled.
Now to try this add a new column of type bit and name it as IsActive to customers table
ALTER TABLE Customer ADD IsActive BIT
The above stored procedure has the section ELSE which deals with the soft delete means it will update the records flag as "0" to indicate that this records is in not use or disabled.
ELSE
UPDATE Customer SET IsActive=0 where CustomerID=@CustomerID
Example :
EXEC dbo.Customer_Delete 2,0
SELECT * FROM Customer
1 Insert SP 0 10 1
2 update sp 20 23 0
4 select sp 20 23 1
5 select sp1 21 24 1
If you see the above results it is clear that row number 2 IsActive flag has been set to 0 means the record is no more in use or it is disabled our queries should be written to check this flag
Stored Procedure For Reader
This is the final and last part plays very important role in the data accessing, fetching or reading part of any database table.
Procedure Script
CREATE PROCEDURE [dbo].[Customer_Reader]
@CustomerID int=null,
@CustomerName varchar(50)=null,
@IsActive bit = null
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM Customer
WHERE
(CustomerID=@CustomerID OR @CustomerID IS NULL)
AND (CustomerName=@CustomerName OR @CustomerName IS NULL)
AND (IsActive =@IsActive OR @IsActive IS NULL)
END
GO
This is simple script and we are declaring all the parameters as optional means you can pass combination of these or none to the procedure. If you dont pass any parameter it will return all the records from table. Or if you pass any combination of these parameters means you can pass any one, two or three of them according to your paramter filteration the result will come.
How It Works
This is pretty simple as all we know truth table for OR & AND as they are like below. We can easily identify the results of it
Just check if I dont pass any parameter to this procedure what will happen
CustomerID=@CustomerID i.e. False
@CustomerID IS NUll i.e. True
False or True i.e. True
Like this others means all filters returns true and And operation returns true. and it returns all the records from the table
Truth table for And Operation
Truth table for OR Operation
Like wise which ever parameter you pass for that if the value is true and the others by default they return true because they are null you will get exact result.
This is how our most of the search operations on websites are implemented.
Example :
EXEC dbo.Customer_Reader
1 Insert SP 0 10 1
2 update sp 20 23 0
4 select sp 20 23 1
5 select sp1 21 24 1
EXEC dbo.Customer_Reader @CustomerID=1
1 Insert SP 0 10 1
EXEC dbo.Customer_Reader @IsActive=1
1 Insert SP 0 10 1
4 select sp 20 23 1
5 select sp1 21 24 1
This is how we should practice with the stored procedures while writing. I hope this tutorial helps you.
The stored procedure is a set of PL/Sql statements written inside a named block. We use stored procedures in Sql Server to perform the very basic operations on database tables.
Advantages
Stored Procedures are very much useful in multidevelopment environment. As they serve the developers or applications from single point.
They are precompiled and the execution of the stored procedures is very fast when compared to sql queries
They sit inside the database and executes from it. The changes made in one place are visible to everybody whoever is accessing the stored procedures.
Stored Procedure Syntax
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
The syntax is very clear starts with the command CREATE PROCEDURE followed by procedure name and the list of parameters that can be passed to it with datatypes and default values. The parameters declared here must be passed from the context from where the procedure is getting executed. If nothing passed for a parameter and any default value assigned to it then the default value will be taken and the procedure executes. If no default value assigned then it will through an error like 'Value does not supplied for <Parameter_1>'
After the parameter declaration we will begin the actual body of the procedure and start writing the business logic and Sql queries for the processing.
Stored Procedures Best Practices
So far we saw the syntax and parameter declaration for the stored procedure. Now this is the time to see how we can write stored procedures for a particular table. What are the best practices to do it.
For any database table the primary thing or primary operations that we can implement are CRUD operations.
C - Create / Insert
R - Read / Select
U - Update / Edit
D - Delete / Remove
These are the basic operations, that every database programmer should implement for every table. The industry standards or the best practices followed by developers are as follows..
Before getting into the practices first create a table with the following statement on you database.
Create Table Script
CREATE TABLE [dbo].[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [varchar](50) NULL,
[YTDOrders] [int] NULL,
[YTDSales] [int] NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
The table is created as CustomerID as integer, identity column and primary key. So what ever operations we perform the primary key is important to perform different kinds of operations like update, select and delete.
Stored Procedure for UPSERT
Don't get frightened by this new word UPSERT, this is name used by DB programmers for stored procedures which performs both insert + update. So the very first step we should consider for our table is to write a procedure which performs both insert and update operations as below.
Procedure Script
CREATE PROCEDURE Customer_Upsert
@CustomerID int=null,
@CustomerName nvarchar(40)=null,
@YTDOrders int=null,
@YTDSales int=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if(@CustomerID is null)
INSERT INTO Customer(CustomerName,YTDOrders, YTDSales) Values(@CustomerName,@YTDOrders,@YTDSales)
else
UPDATE Customer SET
CustomerName=coalesce(@CustomerName, CustomerName),
YTDOrders=coalesce(@YTDOrders,YTDOrders),
YTDSales=coalesce(@YTDSales,YTDSales)
WHERE CustomerID=@CustomerID
END
GO
Here we are passing all the columns values as parameters and we are assigning them with their respective default values. The advantage of assigning default values is when application developers working with this procedure and they forgot to pass few parameter values then it should break. And the default value assignment is very much useful when we are performing some search kind of filtering and on update operations.
Here we are checking whether the @CustomerID is passed or not? for new records as they wont have any primary column value it will be passed empty and our stored procedure will identifies it as new records and the insertion statement executes.
Try with this sample statement:
EXEC Customer_Upsert @CustomerName='Insert SP',@YTDOrders='13',@YTDSales='4'
And now execute the select statement to see the records in the table
Result after Upsert Stored Procedure Performed Insert
If the @CustomerID is passed our Stored Procedure will identify as it is already exists and executes the Update statement accordingly.
Here the interesting point we should see is COALESCE, this is function is very very useful while updating. Suppose consider in some scenario I have updated only few column values for a customer. I want to update the sales of a customer then I will pass only the new value for sales what about other column values if I dont pass their values if I do not use COALESCE i will end up in some permutations and combinations as I need to write conditions like if only customer name passed or customer name plus sales like this. Now it COALESCE solves my problem and provide simple single line statement to finish my job.
What it will do is. It checks whether the passed value is null or not. If it is null it will assign the old value to it
other wise the new value.
Try with this simple Sql Statement
EXEC Customer_Upsert @CustomerID=1,@YTDSales='10'
Result after upsert stored procedure executes update
Now see the result and compare with the old result select after insert.
This is how we one should write Upsert stored procedure, which will provide two kinds functionality to insert and update.
Stored Procedure For Delete
This is very simple procedure to do, and requires only primary key or any combination of columns. But the delete is of 2 kinds.
Procedure Script
USE [SampleDAC]
GO
/****** Object: StoredProcedure [dbo].[Customer_Delete] Script Date: 01/08/2013 23:28:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Customer_Delete]
@CustomerID int,
@DeletePermanent bit
AS
BEGIN
SET NOCOUNT ON;
IF @DeletePermanent = 1
DELETE FROM Customer WHERE CustomerID=@CustomerID
ELSE
UPDATE Customer SET IsActive=0 where CustomerID=@CustomerID
END
GO
1.Permanent Delete: Here we delete entire row for a passed primary key. The above stored procedure has the section IF to deal with this functionality
IF @DeletePermanent = 1
DELETE FROM Customer WHERE CustomerID=@CustomerID
Example:
EXEC dbo.Customer_Delete 3,1
SELECT * FROM Customer
1 Insert SP 0 10 1
2 update sp 20 23 0
4 select sp 20 23 1
5 select sp1 21 24 1
This shows the results it deleted the records number 3 permanently from the database
2. Soft Delete: Here we will make a row as disabled by means of maintaining a bit column for that table. If the bit value is 1 it is active other wise it is disabled.
Now to try this add a new column of type bit and name it as IsActive to customers table
ALTER TABLE Customer ADD IsActive BIT
The above stored procedure has the section ELSE which deals with the soft delete means it will update the records flag as "0" to indicate that this records is in not use or disabled.
ELSE
UPDATE Customer SET IsActive=0 where CustomerID=@CustomerID
Example :
EXEC dbo.Customer_Delete 2,0
SELECT * FROM Customer
1 Insert SP 0 10 1
2 update sp 20 23 0
4 select sp 20 23 1
5 select sp1 21 24 1
If you see the above results it is clear that row number 2 IsActive flag has been set to 0 means the record is no more in use or it is disabled our queries should be written to check this flag
Stored Procedure For Reader
This is the final and last part plays very important role in the data accessing, fetching or reading part of any database table.
Procedure Script
CREATE PROCEDURE [dbo].[Customer_Reader]
@CustomerID int=null,
@CustomerName varchar(50)=null,
@IsActive bit = null
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM Customer
WHERE
(CustomerID=@CustomerID OR @CustomerID IS NULL)
AND (CustomerName=@CustomerName OR @CustomerName IS NULL)
AND (IsActive =@IsActive OR @IsActive IS NULL)
END
GO
This is simple script and we are declaring all the parameters as optional means you can pass combination of these or none to the procedure. If you dont pass any parameter it will return all the records from table. Or if you pass any combination of these parameters means you can pass any one, two or three of them according to your paramter filteration the result will come.
How It Works
This is pretty simple as all we know truth table for OR & AND as they are like below. We can easily identify the results of it
Just check if I dont pass any parameter to this procedure what will happen
CustomerID=@CustomerID i.e. False
@CustomerID IS NUll i.e. True
False or True i.e. True
Like this others means all filters returns true and And operation returns true. and it returns all the records from the table
Truth table for And Operation
Truth table for OR Operation
Like wise which ever parameter you pass for that if the value is true and the others by default they return true because they are null you will get exact result.
This is how our most of the search operations on websites are implemented.
Example :
EXEC dbo.Customer_Reader
1 Insert SP 0 10 1
2 update sp 20 23 0
4 select sp 20 23 1
5 select sp1 21 24 1
EXEC dbo.Customer_Reader @CustomerID=1
1 Insert SP 0 10 1
EXEC dbo.Customer_Reader @IsActive=1
1 Insert SP 0 10 1
4 select sp 20 23 1
5 select sp1 21 24 1
This is how we should practice with the stored procedures while writing. I hope this tutorial helps you.
.jpg)