Create SQL Server Stored Procedures, Views and Functions
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.
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', '[email protected]' , 1), ('Jane Doe', '012 365 4789', '[email protected]' , 2), ('John Smit', '012 365 4789', '[email protected]' , 1), ('Eddy Jones', '012 365 4789', '[email protected]' , 4), ('Steve Dobson', '012 365 4789', '[email protected]', 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 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.
You can read more about stored procedures, views, and functions in the following articles:
- SQL Server Stored Procedure Tutorial
- SQL Server Views Getting Started
- SQL Server Functions System Tips
About the author
View all my tips
Article Last Updated: 2022-10-18