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 useful in multi development environment as they serve developers or applications from a 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 execute from it. The changes made in one place are visible to everybody who is accessing the stored procedures.
Stored Procedure Syntax
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
<@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;
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
The syntax is very clear. It 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 is 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 is assigned, then it will throw 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 your 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 whatever 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 the name used by DB programmers for stored procedures which perform 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;
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 are working with this procedure and they forgot to pass few parameter values, then it should break. And the default value assignment is very 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 won't have any primary column value, it will be passed empty and our stored procedure will identify 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 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 don't 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 COALESCE solves my problem and provides a 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, otherwise 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 of functionality to insert and update.
Stored Procedure For Delete
This is a 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
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 otherwise 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 which means it will update the records flag as "0" to indicate that this record is not in 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 which plays a 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 a simple script and we are declaring all the parameters as optional which means you can pass combination of these or none to the procedure. If you don't pass any parameter, it will return all the records from table. Or if you pass any combination of these parameters, it means you can pass any one, two or three of them according to your parameter filteration, the result will come.
How It Works
This is pretty simple as we all know truth table for OR & AND as they are like below. We can easily identify the results of it.
Just check if I don't 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 mean 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
|
Likewise, whichever 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 most of the search operations on websites are implemented.
For 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.
