Executive Summary
We’ll review Stored Procedures in this lesson. Stored Procedures allow for modular and efficient operations by having one centralized repository. We’ll review what they are, how they can be beneficial, and effective utilization. As usual, hands-on examples will be below as well.
Introduction
A Stored Procedure is a precompiled SQL Statement stored under a name and processed as a complete unit. Stored Procedures can have parameters utilized to allow specificity as needed.
CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO
Why use Stored Procedures?
- Performance: Stored Procedures are compiled once and stored in an executable form, allowing for quick and easy procedure calls.
- Modularity: Stored Procedures allow for the creation of modules in your SQL environment.
- Security: Direct access to data can be limited by only allowing data modifications through a specific process.
Executing Stored Procedures:
To execute a stored Procedure, the ‘Execute’ command or ‘Exec’ command can be utilized in combination with the procedure name.
--Execute SP SelectAllCustomers
EXECUTE SelectAllCustomers;
Stored Procedures & Parameters
Stored procedures can include parameters, making them much more flexible.
- Creating Stored Procedure with Parameter:
--Create SP & Parameter @CustomerID
CREATE PROCEDURE SelectCustomerByID
@CustomerID INT
AS
SELECT * FROM Customers WHERE ID = @CustomerID
GO
- Executing Stored Procedure with Parameter:
-- Execute SP and Pass Customer ID.
EXECUTE SelectCustomerByID @CustomerID = 1
Conclusion:
Utilizing Stored Procedures can allow you to write more modular, efficient, and secure.
What are some ways you may be able to utilize Stored Procedures in your day to day life? How about querying a personal addresses for low level users, without giving them full access? What If we wanted to use the same code block across 10+ scripts.
In the future, we’ll discuss triggers in SQL and how to automatically run code in responses to certain events in a database.