Stored Procedure vs Function

By Tiationg Kho |
Tags:

Stored Procedure vs Function

FeatureStored ProcedureFunction
UsageUse for complex processing (involve DML, transaction and exception handling)Use for computations
Database ModificationsYes (INSERT, UPDATE, DELETE)No
SyntaxUse EXEC to runUsed in SELECT statement
ParametersInput, output, input/outputInput only
Return Type1. Without RETURN, automatically implicit return a scalar value. 2. With RETURN, return a scalar value. 3. Without RETURN, use SELECT to return a result setMust return a value, which can be a scalar value or a table value
Call each otherCan call functionCannot call stored procedure

Stored Procedure

CREATE PROCEDURE UpdateUserEmail
    @UserID INT,
    @NewEmail VARCHAR(255)
AS
BEGIN
    UPDATE Users
    SET Email = @NewEmail
    WHERE ID = @UserID;
END;
GO

EXEC UpdateUserEmail @UserID = 1, @NewEmail = 'newemail@example.com';

CREATE PROCEDURE GetUserName
    @UserID INT,
    @UserName VARCHAR(100) OUTPUT
AS
BEGIN
    SELECT @UserName = Name FROM Users WHERE ID = @UserID);
END;
GO

DECLARE @RetrievedUserName VARCHAR(100);
EXEC GetUserName @UserID = 1, @UserName = @RetrievedUserName OUTPUT;

Function

CREATE FUNCTION CalculateArea(@radius FLOAT)
RETURNS FLOAT
AS
BEGIN
    DECLARE @area FLOAT;
    SET @area = PI() * @radius * @radius;
    RETURN @area;
END;
GO

SELECT CalculateArea(10) AS Area;

177. Nth Highest Salary

CREATE FUNCTION getNthHighestSalary(@N INT)
RETURNS INT
AS
BEGIN
    DECLARE @res INT;

    WITH cte AS (
        SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS [rank]
        FROM Employee
    )
    SELECT @res = 
        MAX(salary)
        FROM cte
        WHERE [rank] = @N;
    
    RETURN @res;
END