Stored Procedure

Introduction

A stored procedure is a group of one or more SQL statements stored under a name and saved in the database. Stored procedures allow you to encapsulate logic, improve performance, and promote reusability, which simplifies complex operations by reducing redundancy.

Stored procedures are especially useful in business applications, like managing customer data, where multiple operations like adding, updating, or deleting records must be performed consistently and efficiently.

Why Use Stored Procedures?

• Modular Design: Break complex logic into manageable, reusable parts.
• Performance Optimization: SQL Server caches the execution plan, making repeated execution faster.
• Security: Restrict user access to data by exposing only stored procedures, keeping the underlying tables protected.
• Reduced Network Traffic: As multiple statements are packaged into one procedure, fewer calls are made to the database, reducing overhead.

Creating a Stored Procedure

In MS SQL, the CREATE PROCEDURE command is used to define a stored procedure.

Syntax:

CREATE PROCEDURE procedure_name 
AS
BEGIN
   -- SQL statements
END;

Example: A stored procedure for adding new customers to a database.

CREATE PROCEDURE AddCustomer
   @customer_name NVARCHAR(100),
   @City NVARCHAR(50),
   @phone NVARCHAR(15)
AS
BEGIN
   INSERT INTO Customers (customer_name, City, phone)
   VALUES (@customer_name, @City, @phone);
END;

Here, we define a procedure named AddCustomer that accepts parameters for customer details and inserts them into the Customers table.

Executing Stored Procedures

To execute a stored procedure, the EXEC or EXECUTE keyword is used.

Example: Executing the AddCustomer procedure to add a new customer.

EXEC AddCustomer @customer_name = 'John Doe', @City = 'New York', @phone = '123-456-7890';

Parameters in Stored Procedures

Stored procedures can accept parameters (input, output, or both).

• Input Parameters: Allow you to pass data into the procedure.
• Output Parameters: Return data from the procedure to the calling program.
• Default Parameters: Provide default values if no input is given.

Example: A procedure to get customer information based on city.

CREATE PROCEDURE GetCustomersByCity
   @City NVARCHAR(50)
AS
BEGIN
   SELECT customer_name, phone 
   FROM Customers 
   WHERE City = @City;
END;

To execute:

EXEC GetCustomersByCity @City = 'New York';

Stored Procedures with Output Parameters

Output parameters allow the procedure to return values.

Example: Get the total number of customers in a specific city.

CREATE PROCEDURE GetCustomerCountByCity
   @City NVARCHAR(50),
   @CustomerCount INT OUTPUT
AS
BEGIN
   SELECT @CustomerCount = COUNT(*) 
   FROM Customers 
   WHERE City = @City;
END;

When executing this procedure:

DECLARE @Count INT;
EXEC GetCustomerCountByCity @City = 'New York', @CustomerCount = @Count OUTPUT;
SELECT @Count AS TotalCustomers;

Here, the procedure returns the count of customers in the city of New York through the @CustomerCount output parameter.

Control Flow in Stored Procedures

Stored procedures support control-of-flow constructs like IF...ELSE, WHILE, and BEGIN...END, which makes them powerful for implementing business logic.

Example: Check if a customer exists before inserting a new one.

CREATE PROCEDURE AddCustomerIfNotExists
   @customer_name NVARCHAR(100),
   @City NVARCHAR(50),
   @phone NVARCHAR(15)
AS
BEGIN
   IF NOT EXISTS (SELECT 1 FROM Customers WHERE customer_name = @customer_name)
   BEGIN
      INSERT INTO Customers (customer_name, City, phone)
      VALUES (@customer_name, @City, @phone);
   END
   ELSE
   BEGIN
      PRINT 'Customer already exists.';
   END;
END;

This procedure ensures that no duplicate customer names are inserted.

Error Handling in Stored Procedures

In MS SQL, you can handle errors using TRY...CATCH blocks within stored procedures. This allows graceful handling of runtime errors without crashing the procedure.

Example: Handling errors during customer insertion.

CREATE PROCEDURE AddCustomerWithErrorHandling
   @customer_name NVARCHAR(100),
   @City NVARCHAR(50),
   @phone NVARCHAR(15)
AS
BEGIN
   BEGIN TRY
      INSERT INTO Customers (customer_name, City, phone)
      VALUES (@customer_name, @City, @phone);
   END TRY
   BEGIN CATCH
      PRINT 'An error occurred while adding the customer.';
      -- You can log or re-throw the error
   END CATCH;
END;

Modifying and Deleting Stored Procedures

Stored procedures can be modified using the ALTER PROCEDURE command and deleted with the DROP PROCEDURE command.

Modifying a Stored Procedure:

ALTER PROCEDURE AddCustomer
   @customer_name NVARCHAR(100),
   @City NVARCHAR(50),
   @phone NVARCHAR(15),
   @Email NVARCHAR(100) -- Adding a new parameter
AS
BEGIN
   INSERT INTO Customers (customer_name, City, phone, Email)
   VALUES (@customer_name, @City, @phone, @Email);
END;

Dropping a Stored Procedure:

DROP PROCEDURE AddCustomer;

Stored Procedures vs. Functions

Stored procedures and functions serve different purposes:

• Stored Procedures: Used to perform actions like insertions, updates, deletions. They can return multiple result sets and output parameters.
• Functions: Return a single value and cannot modify the database (read-only).

Benefits of Using Stored Procedures

• Consistency: Business rules are centralized and applied uniformly when inserting, updating, or deleting customer data.
• Security: Stored procedures can help secure data access by limiting direct access to tables.
• Improved Performance: Procedures are precompiled, reducing execution time for frequent operations like customer lookups, updates, or reports.

Conclusion

Stored procedures are a vital tool for managing data in MS SQL, especially in scenarios involving complex business logic or frequent data manipulations, such as customer management systems. They help streamline operations, improve performance, and ensure data integrity.

Asked in Interview

• What is the difference between a Stored Procedure and a Function in SQL?
• How do you handle errors in a stored procedure?
• What is a recursive stored procedure? Can you create one?
• What are some performance optimization techniques for stored procedures?
• How do stored procedures enhance security in SQL databases?
• How would you troubleshoot a stored procedure that is running slower than expected?