SQL Advanced 3: Stored Procedures
SQL
Our latest blog post delves into the world of advanced SQL techniques. Learn how stored procedures can revolutionize your data management processes, from code reusability to enhanced security. Discover the step-by-step process of creating, executing, and modifying stored procedures, and gain insights into real-world applications. Elevate your SQL skills and efficiency with this essential tool. Read now to unlock the potential of stored procedures!
Greetings, everyone! In this part of our SQL Advanced series, we're diving into the topic of Stored Procedures. As we briefly touched upon this in our previous post, let's explore how this feature can bring added value to our SQL toolkit.
When it comes to handling and working with data, the structured query language (SQL) for relational databases is incredibly important. One powerful feature that SQL offers is the concept of stored procedures. Stored procedures allow us to create reusable and efficient pieces of code that can be executed on the database server. In this beginner's guide, we'll explore what stored procedures are, why they are useful, and how to create and use them.
What are Stored Procedures in SQL?
A stored procedure is a collection of SQL statements that are precompiled and stored in the database server. They are designed to encapsulate a specific task or set of tasks, allowing us to execute complex operations on the database without having to write the entire SQL logic each time.
Think of stored procedures as customizable functions that are stored in the database itself.
Benefits of Using Stored Procedures in SQL:
Code Reusability: By creating a stored procedure, we can reuse the same set of SQL statements across different parts of our application. This not only saves time but also promotes consistency and reduces the likelihood of errors.
Improved Performance: Stored procedures are precompiled and stored in the database server. This means that the execution plan is already optimized, leading to faster query performance. Additionally, network traffic is reduced as only the procedure call needs to be transmitted.
Enhanced Security: Stored procedures can help improve security by allowing us to control access to the database. Instead of granting direct table-level permissions, we can grant permissions to execute specific procedures, limiting exposure to sensitive data.
Simplified Maintenance: If there are changes required in the logic or business rules, we can modify the stored procedure once and have the changes reflected throughout our application, rather than updating multiple instances of the same logic.
Creating a Stored Procedure in SQL:
Creating a stored procedure involves defining the SQL statements that make up the procedure's functionality and then storing it in the database.
Here's a very basic example of creating a stored procedure to retrieve employee information:
If you like this content and want to learn more, you might also read:
SQL Basics 1: How to Create Table and Add Data in SSMS
SQL Basics 2: SELECT + FROM Statements
SQL Basics 4: GROUP BY and ORDER BY Statements
SQL Intermediate 1: INNER and OUTER Joins
SQL Intermediate 2: UNION Operator:
SQL Intermediate 3: CASE Statement
SQL Intermediate 4: HAVING Clause
SQL Intermediate 5: UPDATE and DELETE Data
SQL Intermediate 6: AS - Aliasing
SQL Intermediate 7: PARTITION BY Clause
SQL Advanced 1: CTEs - Common Table Expressions
SQL Advanced 3: Stored Procedures
SQL Advanced 4: SUBQUERIES - In Select, From and Where
SQL Advanced 5: String Functions - TRIM, LTRIM, RTRIM, REPLACE, SUBSTRING, UPPER, and LOWER
To find it:
Database ->
SQL_Basics (How we named our database in SQL Basics 1) ->
Programmability ->
Stored Procedures (Refresh to see the update) ->
‘GetEmployeeINFO”
Executing a Stored Procedure in SQL:
To execute a stored procedure, we use the EXECUTE or CALL statement, followed by the procedure name and any required parameters.
Here's how we would execute the ‘GetEmployeeINFO’ procedure:
EXEC GetEmployeeINFO
Now, let’s make it a little spicier using our temp table from past exercise – SQL Advanced 2.
CREATE PROCEDURE Temp_Employee AS
CREATE TABLE #Temp_Employee
(JobTitle varchar (50),
EmployeesPerJob int,
AvgAge int,
AvgSalary int)
INSERT INTO #Temp_Employee
SELECT JobTitle, COUNT(JobTitle), AVG(Age), AVG(Salary)
FROM EmployeeDemographics AS DEMO
JOIN EmployeeSalary AS Sal
ON DEMO.EmployeeID = Sal.EmployeeID
GROUP BY JobTitle
SELECT *
FROM #Temp_Employee
In order to execute this query, we need to have a SELECT statement in this stored procedure.
And let’s execute it:
EXEC Temp_Employee
How to Modify Stored Procedures in SQL:
To modify an existing stored procedure, we can right click on stored procedure we want to modify and ‘Modify’.
By adding this parameter, we are specifying the JobTitle.
Let’s say @JobTitle = Analyst, it will become Analyst and it will give us results based off Analyst.
If we EXECUTE it without specifying JobTitle, we get this error:
To fix it, we need to include our parameter of JobTitle.
EXEC Temp_Employee @JobTitle = 'Analyst'
What shows in our screen is:
USE [SQL_Basics]: Specifying the database.
SET ANSI_NULLS ON: This command instructs SQL Server to follow the ANSI SQL standard behavior when dealing with NULL values. In this mode, comparisons and operations involving NULL values follow the three-valued logic of true, false, or unknown.
SET QUOTED_IDENTIFIER ON: This command specifies that SQL Server should use the ANSI SQL standard for quoted identifiers. When this setting is on, double quotation marks (") are used to enclose identifiers (such as column or table names) instead of square brackets ([]).
ALTER PROCEDURE: This statement in SQL is used to modify an existing stored procedure. It allows us to make changes to the logic, parameters, or other attributes of a stored procedure without needing to recreate it from scratch.
To alter, we have to add a parameter, which allows us to specify the input into the stored procedures so we can get this specific result back.
Here's the basic syntax for using the ALTER PROCEDURE statement:
ALTER PROCEDURE [dbo].[Temp_Employee]
AS
@JobTitle nvarchar(100)
AS
CREATE TABLE #Temp_Employee
(JobTitle varchar (50),
EmployeesPerJob int,
AvgAge int,
AvgSalary int)
INSERT INTO #Temp_Employee
SELECT JobTitle, COUNT(JobTitle), AVG(Age), AVG(Salary)
FROM EmployeeDemographics AS DEMO
JOIN EmployeeSalary AS Sal
ON DEMO.EmployeeID = Sal.EmployeeID
WHERE JobTitle = @JobTitle
GROUP BY JobTitle
SELECT *
FROM #Temp_Employee
The output is the result set containing details of all employees who hold the ‘Analyst’ job title.
We don’t need to limit to only one parameter, we can use as many as we like.
Hope you understood a little better Stored procedure. They allow us to encapsulate complex logic and operations, making it easier to manage and work with databases in various applications. As we delve deeper into SQL development, mastering the creation and utilization of stored procedures can significantly enhance our efficiency and effectiveness as a data analyst.