You are currently viewing Beginners Guide to Stored Procedures in SQL

Beginners Guide to Stored Procedures in SQL

Have you ever found yourself writing the same complex SQL queries over and over again? Do you crave a way to streamline your database interactions and boost your coding efficiency? Well, look no further than the magic of SQL stored procedures!

While stored procedures offer significant benefits, they can be intimidating to beginners. In this blog post, we will explore stored procedures, their capabilities and the numerous benefits they bring to the table. We will cover what they are, why you should use them, how they can revolutionize the way you interact with your databases, break down the complexity, and make them approachable. Let’s elevate your SQL game to the next level!

WHAT IS A SQL STORED PROCEDURE?

A stored procedure is a pre-written SQL query that can be run with a single command and is stored within the database itself. It is often used for tasks that need to be done repeatedly. It acts like a mini-program that you can call upon to execute specific tasks on your data. Imagine you’re a chef in a busy kitchen. Every day, you need to prepare the same complex dish for multiple customers. Instead of writing the recipe out every time, you create a “stored recipe” that outlines all the steps involved. This is essentially what a stored procedure is in the database world.

WHY ARE THEY IMPORTANT?

Stored procedures in SQL are a valuable tool for data analyst, developers, database administrators and all SQL users looking to optimize their database operations and ensure data integrity. They offer a powerful way to streamline database interactions, improve efficiency, enhance security, and promote better code organization and maintainability. Here are a few key points about Stored Procedures:

Reusability: This is the key advantage of stored procedures. The ability to eliminate the need to rewrite the same complex SQL queries multiple times. Once a stored procedure has been defined, you can call it by its name at every point of need, to execute the contained query. For example, you are working with a database for an online store, and you have a complex query that involves updating customer information which you need to write every time a customer places an order. You can create a stored procedure called “ProcessOrder”. This procedure contains all the steps involved in handling an order and updating customer details, providing it with the relevant customer information and order details.

Parameter Acceptance: Parameters are inputs that stored procedures accept allowing you to customize the behavior of the procedure, based on specific values you provide. This makes them incredibly versatile for handling different scenarios within the same logic.

Logic and Control Flow: Stored procedures can contain various control flow statements like IF-ELSE conditions and loops, enabling you to perform more complex tasks than simple data retrieval. This allows for more sophisticated data manipulation and processing.

CREATING SQL STORED PROCEDURES

We will be making use of the AdventureWorks Database in SQL Server to explore stored procedures in SQL. To avoid the hassle of installing SQL Server and importing the AdventureWorks Database, use this guide. To create a stored procedure, we use the command “CREATE PROCEDURE”. Let’s create a simple stored procedure that retrieves the Suffix, FirstName, Middle Name, LastName columns from the Person.Person table in the AdventureWorks database.


CREATE PROCEDURE Employee_name
AS
BEGIN
SELECT Suffix, FirstName, MiddleName, LastName
FROM Person.Person
END;

Note: If you don’t specify a schema, by default the stored procedure gets stored in the dbo (database owner) schema.

You can execute the procedure by clicking the F5 keyboard button or the Execute button available in SSMS. The procedure can be found in the object explorer by following this path, AdventureWorks2022 > Programmability > Stored Procedures

Let’s dissect the stored procedure above:

  • CREATE PROCEDURE is the command used to initiate the creation of a stored procedure.
  • Following the create command is the name of the stored procedure “employee_name”.
  • The “AS” keyword indicates the start of the procedure body.
  • The “BEGIN” block signifies the beginning of the executable code within the stored procedure.
  • The “SELECT” statement retrieves data from the database. Here, it selects four columns Suffix, FirstName, MiddleName, LastName
  • The “FROM” clause specifies the table from which to retrieve data. In this case, it’s Person.Person, which likely refers to a table named Person within a schema named Person.
  • The “END” block marks the end of the executable code within the stored procedure.

EXECUTING SQL STORED PROCEDURES

After you have successfully written and executed your stored procedure to store it in your database, to call upon the stored procedure we can make use the command called EXEC or EXECUTE. To call the “employee_name” procedure we wrote earlier, we can use the query below:


EXECUTE Employee_name


EXEC Employee_name

An alternative to writing to queries to call upon a stored procedure is by right clicking the stored procedure in the object explorer and clicking the EXECUTE option.

MODIFYING THE SQL STORED PROCEDURES

Good news!! You can alter or modify an already existing stored procedure to add or remove a chunk of code or comment. Modifying existing stored procedures allows you to adapt them to changing requirements, fix bugs, or optimize performance. To modify a procedure, we use the “ALTER PROCEDURE” command. Let’s alter the employee_name procedure to add the Title column using the query below.


ALTER PROCEDURE Employee_name
AS
BEGIN
SELECT Suffix, FirstName, MiddleName, LastName, Title
FROM Person.Person;
END;

view raw

alter_procedure

hosted with ❤ by GitHub

We can also alter an existing stored procedure by right clicking the stored procedure in the object explorer and clicking the MODIFY option.

Upon clicking the MODIFY option, a new query window opens up, just like the image below. You can then edit to suit the new changes.

USING PARAMETERS IN SQL STORED PROCEDURES 

Parameters are special variables used within stored procedures to provide dynamic input values. They act like placeholders that you can fill with specific data when you execute the procedure. It offers several advantages over hardcoding values directly into your SQL statements. Parameters ensures and improves the flexibility, reusability, maintainability of your stored procedure.

To use a parameter, it must be defined and declared when creating the stored procedure. This includes specifying the parameter name and the data type it can accept. Occasionally you can specify whether it’s an input parameter (providing data to the procedure), an output parameter (returning data from the procedure), or both. Within the query in the stored procedure, the parameter can be referenced by its name, as needed.

Let’s include a parameter in the employee_name stored procedure using the query below:


ALTER PROCEDURE Employee_name
@suffix varchar(5) — Specify data type and size
AS
BEGIN
SELECT Suffix, FirstName, MiddleName, LastName, Title
FROM Person.Person
WHERE Suffix = @suffix; — Filter by the provided suffix
END;

view raw

para_procedure

hosted with ❤ by GitHub

From the above query we altered the employee_name to add a parameter named @Suffix. To execute a stored procedure with a parameter, we must include the parameter input in our query. Let’s include an input to get records of people with the suffix ‘Sr.’, using the query below.


EXEC Employee_name @suffix = 'Sr.';

view raw

exec_para

hosted with ❤ by GitHub

RENAMING THE SQL STORED PROCEDURES

You can easily rename a stored procedure by using the command “SP_RENAME”. Let’s rename the employee_name procedure to employee_names, using the query below


sp_rename 'Employee_name','Employee_names'

NOTE: When renaming the first name is the old initial stored procedure name and the second name is the new stored procedure name.

An alternative of renaming stored procedure is by right clicking the stored procedure in the object explorer and clicking the RENAME option.

DROPPING SQL STORED PROCEDURE

Dropping a SQL stored procedure is a process of removing its definition from the database. The primary method for dropping stored procedures involves using the “DROP PROCEDURE” statement. Let’s drop the employee_names using the “DROP PROCEDURE” statement:


DROP PROCEDURE dbo.employee_names

view raw

drop_procedure

hosted with ❤ by GitHub

An alternative way of dropping stored procedure is by right clicking on the stored procedure in the object explorer and clicking the DELETE option.

BEST PRACTICES FOR WRITING EFFICIENT STORED PROCEDURES IN SQL

  • Write clear and well-commented SQL code within your stored procedures to ensure they are maintainable.
  • Utilize consistent indentation to improve code structure and readability.
  • Avoid putting too much logic into stored procedures as this can make them difficult to debug and test.
  • Employ parameters for user input instead of hardcoding values within the stored procedure.
  • Avoid using “SELECT *” as it retrieves all columns, potentially impacting performance. Specify the required columns instead.
  • Always utilize efficient “WHERE” clauses to filter data precisely.
  • Utilize temporary tables strategically for complex data manipulation within the stored procedure. However, use them judiciously as they can consume memory.
  • Regularly review and optimize your stored procedures for performance improvements.

CONCLUSION

SQL stored procedures are invaluable tools for efficient database management, offering performance optimization, code reusability, and enhanced data integrity. By mastering stored procedures and adhering to best practices, developers can streamline database interactions, improve code organization, and achieve optimal performance. Keep practicing and refining your skills to harness the full potential of SQL stored procedures in your projects.

 

Leave a Reply