Stored Procedures: The Secret to Improving Your Database Applications

Imagine a single command that can execute complex operations, streamline database interactions, and enhance security, all while reducing network traffic.

Stored procedures are the secret weapon in every seasoned developer's toolkit, offering not just a way to execute SQL statements, but a gateway to optimizing performance, simplifying maintenance and protecting your data.

In this exploration, we delve into the world of stored procedures, unraveling their capabilities and revealing how they revolutionize the way databases are harnessed.

Table of Contents

1. Introduction to Stored Procedures
   • What are stored procedures?
   • Why use stored procedures?
   • What are the limitations of using stored procedures?

2. Creating and Using Stored Procedures

   • Create a stored procedure
   • Types of parameters that can be used with stored procedures
   • Call / execute a stored procedures
   • Alter / modify a stored procedure.
   • Drop / delete a stored procedure.

3. Best Practices for Developing Stored Procedures

4. Inventory Management Project

   • Retrieve all items in items
   • Add an item to items
   • Update the quantity of an item in items
   • Retrieve items by name
   • Delete an item from items

5. Conclusion

Introduction to Stored Procedures

What are stored procedures?

A stored procedure is a group of SQL statements that are stored together as a unit.

Why use stored procedures?

Using stored procedures in a database driven application offers a range of benefits that contribute to improved performance, security, maintainability, and overall efficiency.

Here are some of the most common benefits:

• Performance: Stored procedures are precompiled and stored in the database. This means that the query execution plan is generated and saved when the procedure is created or modified, thus leading to faster query execution and reduced overhead.

• Security: Stored procedures can improve the security of your database applications by hiding the underlying SQL code from users.

  This can help prevent unauthorized users from making unauthorized changes to your database.

• Maintainability: Stored procedures can improve the maintainability of your database applications by centralizing the code for a particular task.

  This makes it easier to find and fix bugs, and to make changes to the code.

• Reusability: Stored procedures can be reused in multiple applications. This can save time and effort in development, and it can also help to ensure that applications are consistent with each other.

• Documentation: Stored procedures can be documented, which can help to improve the understanding of how your database applications work.

• Network Traffic: With stored procedures, only the procedure name and input parameters need to be sent to the database server, minimizing the amount of data transferred over the network.

• Compatibility Across Platforms: Since stored procedures provide a consistent interface to the database regardless of the application's programming language or framework, integration and collaboration among different components of a system is easier.

What are the limitations of using stored procedures?

While stored procedures offer numerous advantages, there are also some limitations and considerations that you as a developer should be aware of:

• Complexity: Stored procedures can be complex to develop and maintain. This is especially true for stored procedures that perform complex tasks or that interact with multiple tables.

• Testing: Stored procedures can be difficult to test. This is because stored procedures are not executed in the same way as regular SQL queries.

• Portability: Stored procedures are not always portable between different database platforms.

  This is because different database platforms have different syntax for stored procedures.

  If you heavily rely on stored procedures, it might become challenging to migrate to a different database platform in the future.

• Security: Stored procedures can be a security risk if they are not properly written and secured.

• Performance Trade-offs: While stored procedures can enhance performance, there can be cases where they might not be as efficient as optimized application code, especially when dealing with complex calculations.

Creating and Using Stored Procedures

In this article we are going to use SQL Server and Azure data studio to create and run our stored procedures.

Create a stored procedure

The syntax for creating a stored procedure:

CREATE PROCEDURE schema_name.procedure_name
(
  parameter_name data_type
)
AS
BEGIN
  SQL statements
END;

• CREATE PROCEDURE statement is used to create a new stored procedure.

• schema_name is the name of the schema in which the stored procedure will be created.

  This option depends on whether you created a schema when developing your database.

• procedure_name is the name of the stored procedure.

• parameter_name is the name of a parameter that can be passed to the stored procedure.

  In instances where the procedure will be used to list all items, you don’t need a parameter.

• data_type is the data type of the parameter. You can specify multiple parameters by separating them with commas.

• AS keyword is used to separate the declaration of the stored procedure from the body of the stored procedure.

• BEGIN and END keywords are used to enclose the body of the stored procedure, where you place your SQL statements and logic that will be executed when the stored procedure is called.

Types of parameters that can be used with stored procedures

There are three main types of parameters that can be used with stored procedures:

1) Input Parameters (IN): Input parameters allow you to pass values into the stored procedure when it's called.

These values can be used within the procedure's logic to perform operations or queries. Input parameters are prefixed with an @ symbol.

CREATE PROCEDURE AddItemToInventory
(
    @ItemID int,
    @ItemName varchar(255),
    @Quantity int
)
AS
BEGIN
INSERT INTO Inventory (ItemID, ItemName, Quantity)
VALUES (@ItemID, @ItemName, @Quantity);
END;

In this example, the stored procedure takes three IN parameters:

• @ItemID: The ID of the item to add to inventory
• @ItemName: The name of the item to add to inventory
• @Quantity: The quantity of the item to add to inventory

The values of these parameters are used by the stored procedure to insert a new row into the Inventory table.

2) Output Parameters (OUT): Output parameters are used to return values from the stored procedure back to the caller.

The stored procedure sets the value of the OUT parameter before it returns from execution.

They must be declared using the OUTPUT keyword and are also prefixed with @.

CREATE PROCEDURE GetNumberOfItemsInInventory
(
 @NumberOfItems int OUT
)
AS
BEGIN
SELECT COUNT(*) INTO @NumberOfItems
FROM Inventory;
END;

In this example, the stored procedure takes one OUT parameter:

• @NumberOfItems: The number of items in inventory

The stored procedure counts the number of rows in the Inventory table and sets the value of the @NumberOfItems parameter to the number of rows.

3) Input/Output Parameters (INOUT): These are parameters that can be used to pass data to the stored procedure and to return data from the stored procedure.

The stored procedure can both read and write the value of the INOUT parameter.

They must be declared using the INOUT keyword and are also prefixed with @.

CREATE PROCEDURE IncreaseQuantityByOne
(
 @ItemID int,
 @Quantity int INOUT
)
AS
BEGIN
UPDATE Inventory
SET Quantity = Quantity + 1
WHERE ItemID = @ItemID;
SET @Quantity = Quantity + 1;
END;

In this example, the stored procedure takes two parameters:

• @ItemID: The ID of the item to increase the quantity of.

• @Quantity: The current quantity of the item.

The stored procedure first updates the quantity of the item by 1. Then, it sets the value of the @Quantity parameter to the new quantity of the item.

Call / execute a stored procedure

To call a stored procedure, you use the EXEC statement.

The syntax for the EXEC statement is as follows:

Without Parameters:

If the stored procedure has no parameters, you can omit the parameter list.

EXEC schema_name.procedure_name;

An example of how to call the GetNumberOfItemsInInventory stored procedure:

EXEC GetNumberOfItemsInInventory;

This will call the GetNumberOfItemsInInventory stored procedure and return the number of items in inventory.

With Parameters:

EXEC schema_name.procedure_name @param1 = value1, @param2 = value2;

An example of how to call the AddItemToInventory stored procedure:

EXEC AddItemToInventory @ItemID = 1, @ItemName = 'T-Shirt', @Quantity = 10;

This will call the AddItemToInventory stored procedure and pass the values 1, 'T-Shirt', and 10 to the stored procedure as parameters.

Alter / modify a stored procedure.

To alter or modify a stored procedure, you use

ALTER PROCEDURE

You can use the ALTER PROCEDURE statement to add new parameters, remove parameters from a stored procedure, or to change the definition of a parameter.

The syntax for the ALTER PROCEDURE statement is as follows:

ALTER PROCEDURE schema_name.procedure_name
AS
New_sql_statements

Here is an example of how to alter the AddItemToInventory stored procedure to add a new parameter:

ALTER PROCEDURE AddItemToInventory
(
  @ItemID int,
  @ItemName varchar(255),
  @Quantity int,
  @Price money
)
AS
BEGIN
  INSERT INTO Inventory (ItemID, ItemName, Quantity, Price)
  VALUES (@ItemID, @ItemName, @Quantity, @Price);
END;

This will add a new parameter called @Price to the AddItemToInventory stored procedure. The @Price parameter will be used to store the price of the item being added to inventory.

Drop / delete a stored procedure

To drop or delete a stored procedure, you use the DROP PROCEDURE statement.

The syntax is as follows:

DROP PROCEDURE schema_name.procedure_name;

For example, to drop the AddItemToInventory stored procedure, you would use the following syntax:

DROP PROCEDURE AddItemToInventory;

This will delete the AddItemToInventory stored procedure from the database.

You should only drop a stored procedure if you are sure that you no longer need it. If you drop a stored procedure that is still in use, it could cause problems with your database applications.

Best Practices for Developing Stored Procedures

• Use meaningful names for stored procedures: The names of your stored procedures should be descriptive and easy to understand.

  This will make it easier for other developers to understand what the stored procedures do.

• Comment your stored procedures: Commenting your stored procedures will make it easier for other developers to understand how the stored procedures work.

  It is also a good idea to comment on the parameters that are passed to the stored procedures and the values that are returned by the stored procedures.

• Use parameters to pass data to and from stored procedures: Using parameters to pass data to and from stored procedures makes your stored procedures more flexible and reusable.

  This is because you can change the values of the parameters without having to change the code in the stored procedure.

• Test your stored procedures thoroughly: It is important to test your stored procedures thoroughly before you put them into production.

  This will help to ensure that your stored procedures are working correctly and that they are not vulnerable to security attacks.

• Use a consistent naming convention for stored procedures: Using a consistent naming convention for stored procedures will make it easier for other developers to understand your code.

  This is especially important if you are working on a team of developers.

• Use error handling in your stored procedures: Error handling is important for any piece of code, but it is especially important for stored procedures.

  This is because stored procedures are often called by other applications, and if an error occurs in a stored procedure, it could cause problems with the application that called it.

• Use stored procedures to encapsulate complex logic: Stored procedures can be used to encapsulate complex logic, which can make your code more readable and maintainable.

This is because you can put all of the complex logic in the stored procedure, and then call the stored procedure from your application code.

• Use stored procedures to improve performance: Stored procedures can be used to improve performance by caching the execution plan for the stored procedure

  This means that the database engine does not have to recompile the execution plan each time the stored procedure is called.

  This can result in significant performance improvements, especially for frequently executed stored procedures.

Inventory Management Project

For this simple project, we are going to have a database named inventory which will contain only one tale.

CREATE DATABASE inventory;

USE inventory; 

CREATE TABLE items
(
  ItemID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  ItemName varchar(255) NOT NULL,
  Quantity int NOT NULL,
  Price money NOT NULL
);

INSERT INTO items (ItemName, Quantity, Price)
VALUES ('Product A', 10, 19.99),
('Product B', 20, 29.99),
('Product C', 15, 24.99),
('Product D', 8, 14.99),
('Product E', 25, 9.99);

Retrieve all items in items

Let’s create a stored procedure to retrieve / view all items in the items table:

CREATE PROCEDURE GetAllItems
AS
BEGIN
  SELECT *
  FROM items;
END;

After executing the stored procedure, it is saved in the database as shown below.

Now, Lets execute it and see the results:

EXEC GetAllItems;

This will return a result set containing all rows from the items table.

Add an item to items

CREATE PROCEDURE AddItem
(
  @ItemName varchar(255),
  @Quantity int,
  @Price money
)
AS
BEGIN
  INSERT INTO items (ItemName, Quantity, Price)
  VALUES (@ItemName, @Quantity, @Price);
END;

Now, Lets execute it and see the results:

EXEC AddItem @ItemName = 'Product F', @Quantity = 10, @Price = 10.99;

This will add a new item to inventory with the name 'Product F', a quantity of 10, and a price of 10.99.

Update the quantity of an item in items

CREATE PROCEDURE UpdateQuantityOfItem
(
  @ItemID int,
  @Quantity int
)
AS
BEGIN
  UPDATE items
  SET Quantity = @Quantity
  WHERE ItemID = @ItemID;
END;

Now, Lets execute it and see the results:

EXEC UpdateQuantityOfItem @ItemID = 1, @Quantity = 20;

This will update the quantity of the item with the ID 1 to 20.

Retrieve items by name

CREATE PROCEDURE RetrieveItemsByName
(
  @ItemName varchar(50)
)
AS
BEGIN
  SELECT
    ItemID,
    ItemName,
    Quantity,
    Price
  FROM items
  WHERE ItemName = @ItemName
END

This stored procedure takes one parameter, @ItemName, which is the name of the item to retrieve.

The stored procedure then selects all rows from the Items table where the ItemName column is equal to @ItemName.

Now, Lets execute it and see the results:

EXEC RetrieveItemsByName @ItemName = 'Product C'

Delete an item from items

CREATE PROCEDURE DeleteItem
(
  @ItemID int
)
AS
BEGIN
  DELETE FROM items
  WHERE ItemID = @ItemID
END

Now, Lets execute it and see the results:

EXEC DeleteItem @ItemID= 4

This will delete the row from the items table where the ItemID column is equal to 1 that is Product D.

Conclusion

By mastering the art of creating, altering, and calling stored procedures, you've acquired a valuable skill set for optimizing data manipulation tasks.

While this article has unveiled the fundamentals and best practices, there's always more to explore.

To further expand your expertise and deepen your understanding, I encourage you to explore more on stored procedures through a comprehensive resource available at SQL Server Tutorial.

There, you'll find a wealth of information, advanced techniques, and real-world use cases that will empower you to harness the full potential of stored procedures in your database driven projects.