9. Stored Procedure vs Function

Let’s consider a table Products:

Table Schema

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Price DECIMAL(10, 2),
    Stock INT
);

Sample Data

ProductID	ProductName	Price	Stock
1	Laptop	1200.00	10
2	Smartphone	800.00	25
3	Headphones	150.00	50
4	Monitor	300.00	20

---

1. Stored Procedure Example

Scenario: Get products with stock greater than a given value.

Stored Procedure:

CREATE PROCEDURE GetProductsByStock
    @MinStock INT
AS
BEGIN
    SET NOCOUNT ON;
 
    SELECT ProductID, ProductName, Price, Stock
    FROM Products
    WHERE Stock > @MinStock;
END

---

Usage:

-- Call stored procedure to get products with stock greater than 15
EXEC GetProductsByStock @MinStock = 15;

Result:

ProductID	ProductName	Price	Stock
2	Smartphone	800.00	25
3	Headphones	150.00	50
4	Monitor	300.00	20

---

2. Function Example

Scenario: Calculate the total value of stock for a product.

Function:

CREATE FUNCTION GetTotalStockValue
(
    @ProductID INT
)
RETURNS DECIMAL(10, 2)
AS
BEGIN
    DECLARE @TotalValue DECIMAL(10, 2);
 
    SELECT @TotalValue = Price * Stock
    FROM Products
    WHERE ProductID = @ProductID;
 
    RETURN @TotalValue;
END

---

Usage:

-- Call the function to get total stock value for ProductID = 2
SELECT dbo.GetTotalStockValue(2) AS TotalStockValue;

Result:

TotalStockValue
20000.00

---

Key Differences Demonstrated

Feature	Stored Procedure Example	Function Example
Return Value	No return value, just SELECT data	Returns a single value
Input/Output Parameters	Input parameter (@MinStock)	Only input parameter (@ProductID)
Usage in SELECT/WHERE	Can’t use SP in SELECT	Can use UDF in SELECT
Transaction Support	Supported	Not supported

---

Functions follow the computer-science definition in that they MUST return a value and cannot alter the data they receive as parameters (the arguments). Functions are not allowed to change anything, must have at least one parameter, and they must return a value. Stored procs do not have to have a parameter, can change database objects, and do not have to return a value.