SQL Stored Procedures - What They Are, Best Practices, Security, and More...

Introduction

This tutorial provides a comprehensive overview of SQL stored procedures, including how to create, call, and manage them effectively. Understanding stored procedures is essential for anyone working with SQL Server, especially for developers using C# in the corporate environment. This guide will equip you with the knowledge to leverage stored procedures for better database management and security.

Chapter 1: Overview of the Demo Database

• Environment Setup: Use SQL Server Management Studio (SSMS) version 17.1 against SQL Server 2016 Developer Edition.
• Database Structure: The demo database, called "Samples," includes a single table:
  • Table Name: dbo.People
  • Records: 8 entries, containing fields for ID, first name, and last name.

Chapter 2: Creating a New Stored Procedure

1. Open a Query Window: Start with a blank query window in SSMS.

2. Define the Stored Procedure:

   • Use the following template for creating a stored procedure:

     CREATE PROCEDURE dbo.SP_People_GetAll
     AS
     BEGIN
         SELECT ID, FirstName, LastName FROM dbo.People
     END
     

   • Key points:
     • Use CREATE PROCEDURE to define a new stored procedure.
     • Use the prefix SP_ to indicate it is a stored procedure (avoid SP_ prefix as it is reserved).
     • The body of the procedure wraps the SQL query in BEGIN and END.

3. Execute the Creation Statement: Run the SQL command to create the stored procedure.

Chapter 3: Calling the Stored Procedure

1. Execute the Stored Procedure:

   • Use the following command to call the procedure:

     EXEC dbo.SP_People_GetAll
     

   • Alternatively, use EXECUTE or EXEC.

2. Refresh Intellisense: If the procedure does not appear, refresh the cache using Ctrl + Shift + R.

3. Understanding Output: Note that the execution message will show the number of rows affected.

Chapter 4: Modifying an Existing Stored Procedure

1. Alter the Stored Procedure:

   • To modify the existing procedure, use:

     ALTER PROCEDURE dbo.SP_People_GetAll
     AS
     BEGIN
         SET NOCOUNT ON;
         SELECT ID, FirstName, LastName FROM dbo.People
     END
     

   • SET NOCOUNT ON prevents the message about the number of rows affected from appearing.

2. Execute the Alter Command: This updates your stored procedure without needing to recreate it.

Chapter 5: Creating a Stored Procedure with Variables

1. Define a New Procedure with Parameters:

   • Create a procedure to filter by last name:

     CREATE PROCEDURE dbo.SP_People_GetByLastName
     @LastName NVARCHAR(50)
     AS
     BEGIN
         SELECT ID, FirstName, LastName FROM dbo.People
         WHERE LastName = @LastName
     END
     

   • Key points:
     • Use @ to define parameters.
     • Specify the data type (e.g., NVARCHAR(50)).

2. Call the Procedure with Parameters:

   • Execute with:

     EXEC dbo.SP_People_GetByLastName @LastName = 'Cory'
     

Chapter 6: Benefits of Stored Procedures

• Security: Limit user access to the database by granting execute permissions on stored procedures only, preventing direct table access.
• Efficiency: Stored procedures are precompiled, making them faster than dynamic queries.
• Reusability: Procedures can encapsulate complex logic, allowing multiple applications to use the same code.
• Reduced SQL Injection Risk: Using parameters in stored procedures decreases the chance of SQL injection attacks.

Chapter 7: Drawbacks of Stored Procedures

• Lack of Source Control: Unlike application code, stored procedures are not easily versioned in standard source control systems.
• Potential Business Logic Issues: Some argue that business logic should reside in application code, not in the database.
• ORM Compatibility: Object-Relational Mappers (ORMs) may not fully leverage stored procedures, limiting their utility.

Conclusion

Stored procedures are a powerful tool in SQL Server, providing significant advantages in terms of security, performance, and manageability. By understanding how to create and utilize them effectively, you can enhance your database applications significantly. Consider exploring more advanced topics, such as transaction management and error handling within stored procedures, to further improve your SQL skills.