SQL Stored Procedures for SQL Server

What is SQL Stored Procedure?

A SQL Stored Procedure is a pre-written set of SQL statements that are stored in a database and can be called or executed on demand by users or applications.

Stored procedures can contain complex business logic, conditional statements, loops, and other programming constructs, making them a powerful tool for database development.

Why and When to use SQL Stored Procedures?

There are several reasons to use SQL Stored Procedures:

Reusability

Once created, stored procedures can be reused multiple times without having to rewrite the code each time. This saves time and reduces the chance of errors.

Security

Stored procedures can be used to control access to sensitive data, by restricting user access to specific parts of the database.

Performance

Stored procedures can improve database performance by reducing the amount of data sent between the application and the database, and by reducing the amount of processing done on the client side.

Maintenance

Stored procedures can be modified and maintained independently of the application code, which can simplify maintenance and reduce the risk of introducing bugs.

Stored procedures are often used in enterprise applications that require complex data processing and manipulation, such as financial systems, inventory management systems, and customer relationship management systems.

How to use SQL Stored Procedures?

To use SQL Stored Procedures, you first need to create them using a CREATE PROCEDURE statement.

The basic syntax of a CREATE PROCEDURE statement is as follows:

CREATE PROCEDURE procedure_name
AS
BEGIN
-- SQL statements to be executed
END

The procedure_name is the name you choose for your stored procedure, and the SQL statements to be executed are placed between the BEGIN and END keywords.

The SQL statements can include SELECT, INSERT, UPDATE, DELETE, and other statements.

Once the stored procedure has been created, you can call it using the EXECUTE statement:

EXECUTE procedure_name

You can also pass parameters to a stored procedure by including them in the CREATE PROCEDURE statement and specifying their values when calling the procedure:

CREATE PROCEDURE procedure_name
@parameter1 datatype,
@parameter2 datatype
AS
BEGIN
-- SQL statements to be executed
END

EXECUTE procedure_name @parameter1 = value1, @parameter2 = value2

Basic Examples of SQL Stored Procedures

Here are some basic examples of SQL Stored Procedures:

Example 1: Creating a Simple Stored Procedure

CREATE PROCEDURE GetCustomerCount
AS
BEGIN
SELECT COUNT(*) AS CustomerCount FROM Customers
END

This stored procedure returns the number of customers in the Customers table.

Example 2: Passing Parameters to a Stored Procedure

CREATE PROCEDURE GetCustomerByID @CustomerID int AS BEGIN 
SELECT 
  * 
FROM 
  Customers 
WHERE 
  CustomerID = @CustomerID END

This stored procedure returns the customer record with the specified ID.

EXECUTE GetCustomerByID @CustomerID = 1

This calls the GetCustomerByID stored procedure with a parameter value of 1.

Advanced Examples of SQL Stored Procedures

Here are some advanced examples of SQL Stored Procedures:

Example 1: Using IF Statements

CREATE PROCEDURE UpdateCustomer @CustomerID int, 
@FirstName varchar(50), 
@LastName varchar(50) AS BEGIN IF EXISTS (
  SELECT 
    * 
  FROM 
    Customers 
  WHERE 
    CustomerID = @CustomerID
) BEGIN 
UPDATE 
  Customers 
SET 
  FirstName = @FirstName, 
  LastName = @LastName 
WHERE 
  CustomerID = @CustomerID 
SELECT 
  'Customer Updated' AS Status END ELSE BEGIN 
SELECT 
  'Customer Not Found' AS Status END END

This stored procedure updates the first and last names of a customer with the specified ID. If the customer record is found and updated, the procedure returns “Customer Updated” as the status.

EXECUTE UpdateCustomer @CustomerID = 1, @FirstName = 'John', @LastName = 'Doe'

This calls the UpdateCustomer stored procedure with parameter values of 1, ‘John’, and ‘Doe’.

Example 2: Using Cursors

CREATE PROCEDURE GetCustomerOrders @CustomerID int AS 
BEGIN 
 DECLARE @OrderID int 
 DECLARE @OrderDate datetime 
 DECLARE @OrderTotal moneyDECLARE OrderCursor CURSOR FOR 
SELECT 
  OrderID, 
  OrderDate, 
  OrderTotal 
FROM 
  Orders 
WHERE 
  CustomerID = @CustomerID OPEN OrderCursor FETCH NEXT 
FROM 
  OrderCursor INTO @OrderID, 
  @OrderDate, 
  @OrderTotal WHILE @@FETCH_STATUS = 0 BEGIN 
SELECT 
  @OrderID AS OrderID, 
  @OrderDate AS OrderDate, 
  @OrderTotal AS OrderTotal FETCH NEXT 
FROM 
  OrderCursor INTO @OrderID, 
  @OrderDate, 
  @OrderTotal END CLOSE OrderCursor DEALLOCATE OrderCursor END

This stored procedure retrieves all orders for a customer with the specified ID, using a cursor to iterate over the result set.

EXECUTE GetCustomerOrders @CustomerID = 1

This calls the GetCustomerOrders stored procedure with a parameter value of 1.

Explanation:

The stored procedure starts by declaring variables for the OrderID, OrderDate, and OrderTotal columns in the Orders table. It then declares a cursor named OrderCursor and defines a SELECT statement to retrieve the orders for the specified customer ID.

The cursor is then opened, and the FETCH NEXT statement retrieves the first row in the result set and stores the values in the variables.

A WHILE loop is used to iterate over the result set. The SELECT statement inside the loop displays the OrderID, OrderDate, and OrderTotal values for each row in the result set.

Finally, the cursor is closed and deallocated to free up system resources.

Conclusion

SQL Stored Procedures are a powerful tool for database development. They offer reusability, security, performance, and maintenance benefits, and can be used to implement complex business logic and data processing. By understanding the basic syntax and examples of SQL Stored Procedures, you can start using them to improve your database development skills and create more efficient and scalable applications.