Stored Procedures: A Practical Example

www.linkedin.com/in/mattdamberg

Photo by Pablo Arroyo on Unsplash

Hello again Medium community! I am back with another SQL article, this time reviewing stored procedures and a recent situation I found myself in where using one made my life a whole heck of a lot easier. As always, if you enjoy the article please hit the like button and consider following me here on Medium for similar content and if you are on LinkedIn send me a connection request for some data driven conversation.

Now, with that out of the way, lets talk about stored procedures!

What Is a Stored Procedure?

A stored procedure is a statement or group of SQL statements which are given a specific name, inside of a database. Executing a stored procedure will run said statement or group of statements without having to physically type them out each time. Instead, all one must do is type in The syntax for creating a stored procedure as well as executing one are both very simple.

--  CREATING A STORED PROCEDURE
CREATE PROCOCEDURE YourProcedureName
AS
BEGIN 
  STATEMENT 1, 
  (STATEMENT 2 OPTIONAL)
END;


-- EXECUTING A STORED PROCEDURE
EXEC YourProcedureName

Benefits Of Using Stored Procedures

The use of stored procedures provides many different benefits to ranging from security to organization and ease of reading. Here are several of what I believe to be the most important benefits to having stored procedures in your SQL skill set.

1. Reusability: allow you to encapsulate a set of SQL statements into a singular, easy to write and reusable code. This simplicity makes the code base easier to maintain and update.
2. Improved Performance: Statements in stored procedures are precompiled and optimized by the DBMS resulting in improved performance and faster query execution.
3. Security and Control: These procedures help enforce security by allowing control over who has ability to execute specific database operations. Much like views, this also allows you to create result sets which are devoid of sensitive and or protected information you would not want everyone having access to.
4. Reduced Network Traffic: Reduce the amount of data transferred between application and database servers. Stored Procedures are executed on servers and almost entirely eliminate the need to transfer data back and forth.
5. Consistency And Data Integrity: Having standardized code as with stored procedures, you reduce the risk of data anomalies or errors resulting from inconsistent code. This also provides a boost for the enforcement of validation logic.

Parameters

In SQL we also have the ability to pass stored procedures parameters for them to act on or around. This essentially means we are able to give the procedure values to accept when it is executed. These parameters control and customize the operation of the stored procedure and allow us to make these stored statement(s) very dynamic. For example, lets say we had a stored procedure called GetEmployeeInfo created with the following code,

-- Define a stored procedure with a parameter
CREATE PROCEDURE GetEmployeeInfo
    @EmployeeID INT -- This is the parameter for the employee's ID
AS
BEGIN
    -- Inside the procedure, you can use the @EmployeeID parameter
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

In this statement we see the creation of a stored procedure called GetEmployeeInfo as well as the establishment of a parameter called employeeID (parameters are always defined with the @ symbol before them). Below that we see the SQL statement which makes up our stored procedure.

Now lets say that we wan to get the info on a the employee with empoyee_id of 123. Simply executing the stored procedure will give us the info on ALL employees which is not what we want. To get this specific data, we need to utilize the parameter we put in place.

-- Call the stored procedure and pass an employee ID as an argument
EXEC GetEmployeeInfo @EmployeeID = 123;

This EXEC statement will get us all the employee information where the employee_id is 123. As you can see, these parameters act as filters and manipulate data for our stored procedures, much the same as WHERE, GROUP BY and ORDER BY do in basic SQL statements. Stored procedures on their own provide enough benefit to make them worth your while to learn them. Adding in parameters and the level of dynamics and simplicity they can bring to your code make getting a firm handle on using stored procedures a monumental asset for you as a SQL user.

Stored Procedure Walkthrough

Recently, I was working on a SQL project where I ran into an issue which called for the use of a stored procedure and I’d like to walk through it with you. I was working with a dataset with multiple tables and one of these tables had values listed as a text data type separated by commas. I needed to join this table to another table to solve a problem I had been working on. This comma separated column was also the only column which could be used as a foreign key to link to this other table I needed to join to.

To solve this issue, I needed to unpivot this column and make each specific comma separated number, a unique value in its own row. I began by creating a temp table to put the split values into. For splitting the values I used the Cross_Apply function with String_Split.

-- CREATE A TEMPORARY TABLE TO HOLD THE RESULTS
CREATE TABLE #Toppings (Topping_ID int, Topping int)

 
-- SPLIT THE COMMA-SEPARATED VALUES AND INSERT INTO THE TEMPORARY TABLE
 INSERT INTO #Toppings (Topping_ID, Topping)
   SELECT PIZZA_ID, CAST(value AS int)
   FROM [dbo].[pizza_recipes]
     CROSS APPLY STRING_SPLIT(Toppings, ',')

With the values split, I was now able to join the temp table I had just created with the original table from the data set. Another temp table was created to store this final result set.

--CREATE NEW TEMP TABLE
CREATE TABLE #INGREDIENTS (TOPPING_ID INT, TOPPING INT, TOPPING_NAME VARCHAR(255))

 --INSERT JOIN STATEMENT INTO NEW TEMP TABLE
 INSERT INTO #INGREDIENTS
 SELECT
  T.TOPPING_ID,     
  T.TOPPING,
  PT.TOPPING_NAME
 FROM #Toppings T LEFT JOIN [dbo].[pizza_toppings] PT ON
  T.TOPPING = PT.TOPPING_ID

Excellent, so now I have my joined table as well as my answer, thats all. Except writing this code out each and every time you need to join these tables or look at this result set would take a long time, run a high risk for error due to confusing code, and (despite how pretty I try and make it) not very readable. Wouldn’t it be nice to simply write one, short line of code and get this same result? Absolutely it would be, and everyone else who ever had to run or use this code would also appreciate it. Lets put it into the much simpler form of a stored procedure.

CREATE PROCEDURE PIZZA_INGREDIENTS
AS 
BEGIN
    -- Create a temporary table to hold the results
    CREATE TABLE #Toppings (Topping_ID int, Topping int)

    -- Split the comma-separated values and insert into the temporary table
    INSERT INTO #Toppings (Topping_ID, Topping)
    SELECT PIZZA_ID, CAST(value AS int)
    FROM [dbo].[pizza_recipes]
    CROSS APPLY STRING_SPLIT(Toppings, ',');

    -- Now that we have the split values, create another temporary table
    CREATE TABLE #INGREDIENTS (TOPPING_ID INT, TOPPING INT, TOPPING_NAME VARCHAR(255));

    -- Insert join statement into the second temporary table
    INSERT INTO #INGREDIENTS
    SELECT
        T.TOPPING_ID,
        T.TOPPING,
        PT.TOPPING_NAME
    FROM #Toppings T
    LEFT JOIN [dbo].[pizza_toppings] PT ON T.TOPPING = PT.TOPPING_ID;
END;



/*////////////////////////// */
EXEC PIZZA_INGREDIENTS    
/*////////////////////////// */

Now instead of having to write out that entire chunk of code, we simply use the EXEC PIZZA_INGREDIENTS and get the same result. While this is good, I am one to always be looking for ways to increase efficiency and make my life easier. And for me, there is one more thing we could do to make this stored procedure even more efficient.

Bonus Tip

The need and timing for running stored procedures varies greatly based on the industry you work. Stored procedures must be rerun each time the SQL session ends or the program is closed. Most of these industries don’t really ever shut their program off. But in my case, my computer gets shut down every night. This would mean that each day I turn it on and reopen SQL, I would need to rerun the stored procedure. However, the flexibility of stored procedures allow us to specify instances where it should be run, such as on start up. This is exactly what I did!

--------------------------------------------------------------------------------------------------------------------------------
-- CREATE PROCEDURE WHICH AUTOMATICALLY RUNS OUR STORED PROCEDURE WHEN SQL STARTS
--------------------------------------------------------------------------------------------------------------------------------

-- Create your startup stored procedure
CREATE PROCEDURE StartupProcedure
AS
BEGIN
    -- Your code here, in this case, we'll call your PIZZA_INGREDIENTS procedure
    EXEC PIZZA_INGREDIENTS;
END;

-- Configure the startup procedure
EXEC sp_procoption 'StartupProcedure', 'STARTUP', 'ON';

Now, each time I open up SQL, this stored procedure runs and saves me valuable time (and having to remember to do it). There are dozens of other ways to customize how and when stored procedures are executed and this was just an example of how efficient and useful these tools can be.

Conclusion

In conclusion, stored procedures in SQL offer a powerful set of tools that can greatly enhance your database management and application development. We’ve explored the numerous benefits they provide, such as reusability, improved performance, enhanced security, reduced network traffic, and data consistency. By encapsulating SQL statements within named procedures, you can streamline your code, make it more secure, and improve the overall efficiency of your database-related tasks.

We also delved into the concept of parameters, which allow you to customize the behavior of stored procedures by passing in specific values when calling them. Parameters add a level of dynamism and versatility to your SQL code, making it easier to create tailored queries without the need to modify the underlying logic of the stored procedure.

Furthermore, we demonstrated a practical example of how stored procedures can simplify complex operations by creating a stored procedure to unpivot data and then running it to join tables, thus saving time and reducing the likelihood of errors.

For those looking to optimize their SQL workflows, we even provided a bonus tip on how to automate the execution of stored procedures at startup, which can be a time-saving solution, especially in cases where your SQL session ends or the system is regularly shut down.

Thanks for reading and if you enjoyed this article, check out my walk through of Part 1 of Danny Ma’s 8 Week SQL Challenge here: https://medium.com/@mattdamberg/dannys-diner-week-1-a-sql-project-c515d1d94a90