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:
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.
Stored procedures can be used to control access to sensitive data, by restricting user access to specific parts of the database.
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.
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:
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.
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.
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.