SQL Stored Procedures, Views and Functions Examples

Problem

When you create any system that interacts with a database, there will be plenty of SQL queries that you need to execute frequently. This can be done by creating stored procedures, views, and functions. These database objects allow you to write the query code, whether simple or complex, once and to easily reuse the code over and over again. These objects will also allow you to build a level of database security and consistency, by limiting how users and applications can interact with the database.

Solution

This SQL tutorial will focus on stored procedures, views, and functions in Microsoft SQL Server with the Transact-SQL (T-SQL) programming language.

When creating an application for a SQL database and having to SELECT, INSERT, UPDATE, and DELETE data frequently, it helps when you have structured objects that can help with the job.

So, for the Companies table in this tutorial, you would typically want to do the following operations:

Select a list of Companies and all their details (SELECT statement)
Select a single Company based on its ID (SELECT statement)
Insert a single Company (INSERT statement)
Update a single Company based on its ID (UPDATE statement)
Delete a single Company based on its ID (DELETE statement)

This is where a stored procedure comes in handy, and in this tutorial, we will be creating stored procedures to handle the operations mentioned above.

Let’s set up the environment with a sample database, tables, primary keys, columns, data types, null properties, constraints, etc. with the following SQL commands which can be run in SQL Server Management Studio (SSMS):

USE master;
GO
 
DROP DATABASE IF EXISTS HRDatabase;
GO
 
CREATE DATABASE HRDatabase;
GO
 
USE HRDatabase;
GO
CREATE TABLE Companies (
   ID            INT CONSTRAINT PK_Companies PRIMARY KEY IDENTITY,
   CompanyName   VARCHAR (80) NOT NULL,
   CompAddress   VARCHAR (80) NOT NULL,
   CompContactNo VARCHAR (20) NOT NULL,
   CreateDate    DATETIME NOT NULL constraint DF_Companies_CreateDate DEFAULT getdate()
)
 
CREATE TABLE Employees (
   ID            INT CONSTRAINT PK_Employees PRIMARY KEY IDENTITY,
   EmployeeName  VARCHAR (80) NOT NULL,
   ContactNo     VARCHAR (20) NOT NULL,
   Email         VARCHAR (80) NOT NULL,
   CompID        INT   NOT NULL,
   CreateDate    DATETIME NOT NULL constraint DF_Employees_CreateDate DEFAULT getdate()
)
 
INSERT INTO Companies (CompanyName, CompAddress, CompContactNo) VALUES
('Alpha Company', '123 North Street, Garsfontein, Pretoria', '091 523 6987'),
('Bravo Company' '456 South Street, Brooklyn, Pretoria'   , '091 523 4789'),
('Charlie Company', '987 West Street, Lynnwood, Pretoria'   , '091 523 1235'),
('Delta Company', '258 East Street, The Meadows, Pretoria', '091 523 7414'),
('Echo Company', '100 Amber Street, Hatfield, Pretoria'   , '091 523 9685')
 
INSERT INTO Employees (EmployeeName, ContactNo, Email, CompID) VALUES
('Joe Blogs', '012 365 4789', 'joeblogs@gmail.com'   , 1),
('Jane Doe', '012 365 4789', 'janedoe@gmail.com'   , 2),
('John Smit', '012 365 4789', 'johnsmit@gmail.com'  , 1),
('Eddy Jones', '012 365 4789', 'eddyjones@gmail.com'   , 4),
('Steve Dobson', '012 365 4789', 'stevedobson@gmail.com', 5)
 
ALTER TABLE dbo.Employees ADD CONSTRAINT
FK_Employees_Companies FOREIGN KEY (CompID) REFERENCES dbo.Companies (ID) 
 
SELECT * FROM Companies
SELECT * FROM Employees

SQL Stored Procedures

SQL stored procedures are implemented as a set of T-SQL queries with a specific name (i.e. procedure name). They are stored in the RDBMS to be used and reused by various users and programs.

Stored procedures can be used to maintain the security of your data by only giving people specific access. End users may view or edit the data but not write the stored procedures themselves, so you can control how data is viewed and modified.

A stored procedure can help maintain data integrity by preventing irregularities from entering the system. It can also improve productivity by requiring less time to enter common statements.

Let’s create some stored procedures on the Companies table to be used in an application with the following syntax:

Stored Procedure to Return a List of All the Companies

USE HRDatabase;
GO
 
CREATE OR ALTER PROCEDURE dbo.GetCompanies -- CREATE PROCEDURE
AS
BEGIN
      SELECT [ID]
            ,[CompanyName]
            ,[CompAddress]
            ,[CompContactNo]
            ,[CreateDate]
      FROM [dbo].[Companies]
END;
 
--To run the Stored Procedure you would run the following SQL code:
EXEC dbo.GetCompanies;

Stored Procedure to Return a Single Company Based on an ID

CREATE OR ALTER PROCEDURE dbo.GetCompany
   @ID int  -- input parameter
AS
BEGIN
      SELECT [ID]
            ,[CompanyName]
            ,[CompAddress]
            ,[CompContactNo]
            ,[CreateDate]
      FROM [dbo].[Companies]
      WHERE ID = @ID
END;
 
--To Execute Stored Procedure you would run the following SQL code:
EXEC dbo.GetCompany @ID = 3;

Stored Procedure to Insert a Single Company

CREATE OR ALTER PROCEDURE dbo.InsCompany
      @CompanyName   varchar(80), -- input parameters
      @CompAddress   varchar(80),
      @CompContactNo varchar(20)
AS
BEGIN
      INSERT INTO [dbo].[Companies]
               ([CompanyName]
               ,[CompAddress]
               ,[CompContactNo]
               ,[CreateDate])
          VALUES
               (@CompanyName
               ,@CompAddress
               ,@CompContactNo
               ,getdate())
END;
 
--To Execute Stored Procedure you would run the following SQL code:
EXEC dbo.InsCompany
      @CompanyName   = 'Zulu-Yankee Company',
      @CompAddress   = '123 Some street, Somewhere Far away, Europe ext 10',
      @CompContactNo= '(999) 852 7401';
 
SELECT * FROM dbo.Companies;

Stored Procedure to Update a Single Company

CREATE OR ALTER PROCEDURE dbo.UpdCompany
      @ID            int          = null,
      @CompanyName   varchar (80) = null,
      @CompAddress   varchar (80) = null,
      @CompContactNo varchar (20) = null
AS
BEGIN
      UPDATE dbo.Companies
      SET CompanyName   = ISNULL(@CompanyName, CompanyName)
         ,CompAddress   = ISNULL(@CompAddress, CompAddress)
         ,CompContactNo = ISNULL(@CompContactNo, CompContactNo)
      WHERE ID = @ID
END
 
--To Execute Stored Procedure you would run the following SQL code:
EXEC dbo.UpdCompany
      @ID            = 6,
      @CompanyName   = 'Zulu-Yanke Company',
      @CompAddress   = null,
      @CompContactNo = '(777) 852 7401'
 
SELECT * FROM dbo.Companies;

Stored Procedure to Delete a Single Company based on its ID

CREATE OR ALTER PROCEDURE dbo.DelCompany
      @ID int
AS
BEGIN
      DELETE FROM dbo.Companies
      WHERE ID = @ID
END;
 
--To Execute Stored Procedure you would run the following SQL code:
EXEC dbo.DelCompany
      @ID = 3;
 
SELECT * FROM dbo.Companies;

In the five subsections above, we have created the stored procedures for the Companies table. You can now create the same stored procedures for the Employees table and any other tables in the database.

Stored procedures should only do the following operations on data:

Insert (Create)
Select (Read)
Update (Update)
Delete (Delete)

There are two approaches regarding stored procedures:

Do the business logic in the stored procedure, or
Let the stored procedure just do the CRUD operations (in brackets above).

You decide what works best for your situation.

There may be environments where nearly all the business logic gets handled in the stored procedures, which can lead to large and bulky stored procedures that are very difficult to handle. Alternatively, there could also be an environment where the business logic is handled in the application and the stored procedures perform the CRUD operations. In your environment, it is necessary to determine how the business logic and CRUD operations will be supported.

SQL Views

SQL views are virtual tables that can be a great way to optimize the database experience. Not only are views good for defining a table without using extra storage, but they also accelerate data analysis and can provide data with extra security.

Benefits of using views:

Security – Views prevent someone from seeing the underlying tables and gives the DBA the option to expose only specific data to users while protecting other data simultaneously.
Simplicity – You can hide complicated queries behind views. They can be hidden from the person browsing the site, but you can reuse the queries easily.
Column Name Simplification or Clarification – Using views, columns can be given aliases to make them more recognizable.
Additional Options – Views can help you process more complex queries. They work in a "multi-level" query and often have more options than standard table queries.

Let us create a simple SELECT query that joins two tables together:

SELECT  c.CompanyName
      , e.EmployeeName
      , e.ContactNo
      , e.Email
FROM dbo.Companies c
JOIN dbo.Employees e on e.CompID = c.ID;

Instead of running the whole SQL query (which can become very big), create a view to return the data.

CREATE VIEW dbo.EmployeeDetailsView
AS
 
   SELECT  c.CompanyName
         , e.EmployeeName
         , e.ContactNo
         , e.Email
   FROM dbo.Companies c
   JOIN dbo.Employees e on e.CompID = c.ID;

After the view is created, you can do a SELECT from the view:

SELECT * 
FROM dbo.EmployeeDetailsView

And you can also filter data from the view:

SELECT * 
FROM dbo.EmployeeDetailsView
WHERE CompanyName = 'Alpha Company';

As seen in the examples above, it can be a good idea to have no filter in the view definition but rather be able to filter data when selecting from the view.

However, it can also be a good idea to hide data from a user that you do not want the user to see.

User-Defined SQL Functions

SQL Server functions are sets of SQL statements that execute a specific task. Their primary use is to allow common tasks to be easily replicated. The use of SQL Server functions is similar to that of functions in mathematics in that they correlate an input variable with output variables.

In SQL Server, you find four different types of functions:

Scalar-valued Functions
Table-valued Functions
Aggregate Functions
System Functions

The idea behind functions is to store them in the database and avoid writing the same code repeatedly.

Let us concentrate on the first two functions.

Create a Scalar-valued Function to Add Two Integers

CREATE or ALTER FUNCTION dbo.udfGetSum(@NumA int, @NumB int)
RETURNS int
AS 
BEGIN 
      DECLARE @SumOfNumbers int
      SELECT @SumOfNumbers = @NumA + @NumB
      RETURN @SumOfNumbers
END;
 
-- Check the function
SELECT dbo.udfGetSum(5,4);

Create a Table-valued Function to Return a Result Set

CREATE or ALTER FUNCTION dbo.udfGetEmployees(@CompID int) 
RETURNS TABLE 
AS 
RETURN 
      SELECT *
      FROM dbo.Companies
      WHERE ID = @CompID;
 
-- Check the function
SELECT * FROM dbo.udfGetEmployees(1);

Above are the most straightforward implementations of functions in SQL Server. You can also use functions to return some values where you do not want to re-type the code repeatedly.

Next Steps

You can read more about stored procedures, views, and functions in the following articles:

Resources

Community

Subscribe