What is the difference between a function and a stored procedure in a database?

A function and a stored procedure in a database differ in several key aspects:

• Return value: A function always returns a value (scalar, table, or other type), whereas a procedure may return nothing or multiple output parameters.
• Usage in queries: Functions can be used within SQL queries (e.g., in SELECT, WHERE), while procedures are called separately using the EXECUTE or CALL command.
• Side effects: Procedures can modify the database state (perform INSERT, UPDATE, DELETE), while functions are usually deterministic and do not modify data (this is a restriction in some RDBMS).
• Parameters: Procedures can have input, output, and input-output parameters, while functions only have input parameters.

Example of a function in SQL:

CREATE FUNCTION GetTotalPrice(@OrderId INT) RETURNS DECIMAL(10,2) AS
BEGIN
    DECLARE @Total DECIMAL(10,2);
    SELECT @Total = SUM(Price * Quantity) FROM OrderDetails WHERE OrderId = @OrderId;
    RETURN @Total;
END

Example of a procedure:

CREATE PROCEDURE UpdateOrderStatus @OrderId INT, @Status VARCHAR(20) AS
BEGIN
    UPDATE Orders SET Status = @Status WHERE Id = @OrderId;
END