SQL Tutorial: Stored Procedures

Stored procedures in SQL Server are precompiled collections of SQL statements and optional control-of-flow statements that are stored under a name and processed as a unit. They can accept parameters, and are used to encapsulate business logic, improve performance, and enforce security.

Introduction to Stored Procedures
Stored procedures offer several benefits:

Performance: SQL Server compiles stored procedures and optimizes the execution plan.
Reusability: Stored procedures can be reused across different applications.
Security: They can restrict direct access to data by providing a controlled interface.
Maintainability: Encapsulate complex business logic in one place.

Creating a Stored Procedure
To create a stored procedure, use the CREATE PROCEDURE statement followed by the procedure name and the SQL statements to be executed.

Syntax:
CREATE PROCEDURE procedure_name
AS
BEGIN
— SQL statements
END;

Example:
Let’s create a stored procedure to fetch all products from the Products table.
CREATE PROCEDURE GetAllProducts
AS
BEGIN
SELECT ProductID, ProductName, Price, Quantity
FROM Products;
END;

Executing a Stored Procedure
To execute a stored procedure, use the EXEC or EXECUTE statement followed by the procedure name.

Example:
EXEC GetAllProducts;

Stored Procedures with Parameters
Stored procedures can accept input parameters and return output parameters.

Syntax for Input Parameters:
CREATE PROCEDURE procedure_name
@parameter_name data_type,
@another_parameter data_type
AS
BEGIN
— SQL statements
END;

Example:
Create a stored procedure to fetch products by a specified price range.
CREATE PROCEDURE GetProductsByPrice
@MinPrice DECIMAL(10, 2),
@MaxPrice DECIMAL(10, 2)
AS
BEGIN
SELECT ProductID, ProductName, Price, Quantity
FROM Products
WHERE Price BETWEEN @MinPrice AND @MaxPrice;
END;

Executing with Parameters:
EXEC GetProductsByPrice @MinPrice = 1.00, @MaxPrice = 2.50;

Output Parameters
Stored procedures can also return output parameters, which can be used to get values back from the procedure.

Syntax for Output Parameters:
CREATE PROCEDURE procedure_name
@parameter_name data_type OUTPUT
AS
BEGIN
— SQL statements
END;

Example:
Create a stored procedure to count the number of products and return it as an output parameter.
CREATE PROCEDURE GetProductCount
@ProductCount INT OUTPUT
AS
BEGIN
SELECT @ProductCount = COUNT(*)
FROM Products;
END;
# Executing with Output Parameters:
DECLARE @Count INT;
EXEC GetProductCount @ProductCount = @Count OUTPUT;
SELECT @Count AS ProductCount;
# Altering a Stored Procedure –
DROP PROCEDURE GetAllProducts;
To modify an existing stored procedure, use the ALTER PROCEDURE statement.
ALTER PROCEDURE GetAllProducts
AS
BEGIN
SELECT ProductID, ProductName, Price, Quantity
FROM Products
ORDER BY ProductName;
END;
# Dropping a Stored Procedure -To delete a stored procedure, use the DROP PROCEDURE statement.
DROP PROCEDURE GetAllProducts;

# Error Handling in Stored Procedures – You can use TRY…CATCH blocks to handle errors in stored procedures.
CREATE PROCEDURE SafeGetProductsByPrice
@MinPrice DECIMAL(10, 2),
@MaxPrice DECIMAL(10, 2)
AS
BEGIN
BEGIN TRY
SELECT ProductID, ProductName, Price, Quantity
FROM Products
WHERE Price BETWEEN @MinPrice AND @MaxPrice;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END;