SQL Server Multi-Level Approval Workflow with Auto Skip Logic

In many enterprise applications like HRMS, Travel Requests, Expense Approval, Purchase Approval , we need a multi-level approval workflow . But real system…

Author

SQLDataDev Editorial Team

Mar 1, 2026 15 min read

In many enterprise applications like HRMS, Travel Requests, Expense Approval, Purchase Approval, we need a multi-level approval workflow.

But real systems have problems like:

Approver not configured
Requester appearing as approver
Same employee appearing multiple times in workflow
Workflow getting stuck

To solve these issues we can design a smart workflow logic in SQL Server that automatically:

✔ Skips invalid levels
✔ Auto-approves missing approvers
✔ Handles duplicate approvers
✔ Stops when next valid approver is found

In this article we will build a complete approval workflow engine using T-SQL.

Step 1 – Create Sample Employees Table

This table stores employee information.

DECLARE @Employees TABLE (
    EmployeeID   INT,
    EmployeeName VARCHAR(50)
);

INSERT INTO @Employees VALUES
(1, 'Ravi Kumar'),
(2, 'Amit Sharma'),
(3, 'Sunita Verma'),   -- DeptHead AND BranchHead (same person)
(4, 'Rakesh Gupta');

Here Sunita appears twice in workflow roles, which is an important real-world scenario.

Step 2 – Create Workflow Table

This table defines approval hierarchy.

DECLARE @WorkFlow TABLE (
    WorkFlowID    INT,
    ReviewLevel   INT,
    ApproverEmpID INT
);

INSERT INTO @WorkFlow VALUES
(1, 1, 1),   -- Level 1 → Ravi
(2, 2, 0),   -- Level 2 → Not set (invalid)
(3, 3, 1),   -- Level 3 → Requester (invalid)
(4, 4, 3),   -- Level 4 → Sunita (DeptHead)
(5, 5, 4),   -- Level 5 → Rakesh (BranchAuth)
(6, 6, 3);   -- Level 6 → Sunita (BranchHead again)

Important situations:

Level	Approver	Situation
2	0	Approver not configured
3	Requester	Invalid approval
6	Sunita again	Same employee appears twice

Step 3 – Create Travel Request Table

This table stores employee requests.

DECLARE @TravelRequest TABLE (
    TravelRequestID INT,
    EmployeeID      INT,
    TripPurpose     VARCHAR(100),
    Status          INT
);

INSERT INTO @TravelRequest VALUES
(94, 1, 'Client Visit Mumbai', 1);

Status values:

Status	Meaning
1	Pending
2	Approved
3	Rejected

Step 4 – Create Approval History Table

This table records every approval action.

DECLARE @ApproverAR TABLE (
    ARID            INT IDENTITY(1,1),
    TravelRequestID INT,
    WorkFlowID      INT,
    ReviewLevel     INT,
    ApproverEmpID   INT,
    ARStatus        INT,
    Remarks         VARCHAR(200),
    ARDate          DATETIME
);

Step 5 – Declare Workflow Variables

These variables control workflow processing.

DECLARE @TravelRequestID INT = 94;
DECLARE @LoginUserEmpID  INT = 2;
DECLARE @ActionStatus    INT = 2;
DECLARE @Remarks         VARCHAR(200) = 'Approved by Sunita';

DECLARE @RequesterEmpID  INT;
DECLARE @MaxLevel        INT;
DECLARE @LoginUserLevel  INT;
DECLARE @LoginUserWFID   INT;

Step 6 – Identify Requester

First we identify who submitted the request.

SELECT @RequesterEmpID = EmployeeID
FROM @TravelRequest
WHERE TravelRequestID = @TravelRequestID;

This is necessary because requester cannot approve their own request.

Step 7 – Get Maximum Workflow Level

SELECT @MaxLevel = MAX(ReviewLevel) FROM @WorkFlow;

This helps us know how many approval levels exist.

Step 8 – Find Last Processed Level

This step ensures we always process the next unprocessed level.

DECLARE @LastProcessedLevel INT;

SELECT @LastProcessedLevel = ISNULL(MAX(ReviewLevel), 0)
FROM @ApproverAR
WHERE TravelRequestID = @TravelRequestID;

Step 9 – Find Login User Workflow Level

If the same user appears multiple times in workflow, we select the next valid level.

SELECT TOP 1
    @LoginUserLevel = ReviewLevel,
    @LoginUserWFID  = WorkFlowID
FROM @WorkFlow
WHERE ApproverEmpID = @LoginUserEmpID
  AND ReviewLevel   > @LastProcessedLevel
ORDER BY ReviewLevel ASC;

This ensures:

✔ Duplicate approvers work correctly
✔ Workflow continues smoothly

Step 10 – Insert Approval Action

Now we record the login user’s approval.

INSERT INTO @ApproverAR
    (TravelRequestID, WorkFlowID, ReviewLevel, ApproverEmpID, ARStatus, Remarks, ARDate)
VALUES
    (@TravelRequestID, @LoginUserWFID, @LoginUserLevel, @LoginUserEmpID, @ActionStatus, @Remarks, GETDATE());

Step 11 – Handle Reject Case

If the approver rejects the request, the workflow stops immediately.

IF @ActionStatus = 3
BEGIN
    UPDATE @TravelRequest
    SET Status = 3
    WHERE TravelRequestID = @TravelRequestID;
    GOTO ShowResult;
END

Step 12 – Process Next Workflow Levels

Now we move to the next approval levels.

DECLARE @NextLevel      INT = @LoginUserLevel + 1;
DECLARE @NeedHuman      BIT = 0;
DECLARE @NextValidEmpID INT = 0;
DECLARE @NextValidLevel INT = 0;
DECLARE @NextValidWFID  INT = 0;
DECLARE @NxtEmpID       INT;
DECLARE @NxtWFID        INT;
DECLARE @ApproverType   VARCHAR(20);

Step 13 – Loop Through Workflow

The system automatically checks each level.

WHILE @NextLevel <= @MaxLevel AND @NeedHuman = 0
BEGIN

    SELECT
        @NxtEmpID = ApproverEmpID,
        @NxtWFID  = WorkFlowID
    FROM @WorkFlow
    WHERE ReviewLevel = @NextLevel;

Step 14 – Determine Approver Type

We classify the approver.

SET @ApproverType = CASE
    WHEN ISNULL(@NxtEmpID, 0) = 0    THEN 'NOT_SET'
    WHEN @NxtEmpID = @RequesterEmpID  THEN 'REQUESTER'
    ELSE                                   'VALID'
END;

Possible results:

Type	Meaning
NOT_SET	Approver not configured
REQUESTER	Requester cannot approve
VALID	Valid approver

Step 15 – Stop When Valid Approver Found

IF @ApproverType = 'VALID'
BEGIN
    SET @NextValidLevel = @NextLevel;
    SET @NextValidEmpID = @NxtEmpID;
    SET @NextValidWFID  = @NxtWFID;
    SET @NeedHuman      = 1;
END

Now workflow waits for next human approval.

Step 16 – Auto Approve Invalid Levels

If a level is invalid, we auto approve it.

ELSE
BEGIN
    INSERT INTO @ApproverAR
        (TravelRequestID, WorkFlowID, ReviewLevel, ApproverEmpID, ARStatus, Remarks, ARDate)
    VALUES (
        @TravelRequestID,
        @NxtWFID,
        @NextLevel,
        ISNULL(@NxtEmpID, 0),
        2,
        CASE @ApproverType
            WHEN 'NOT_SET'   THEN 'Auto Approved (Approver not set)'
            WHEN 'REQUESTER' THEN 'Auto Approved (Requester is Approver)'
        END,
        GETDATE()
    );

    SET @NextLevel = @NextLevel + 1;
END
END

Step 17 – Final Approval

If no more human approvals are needed:

IF @NeedHuman = 0
BEGIN
    UPDATE @TravelRequest
    SET Status = 2
    WHERE TravelRequestID = @TravelRequestID;

    PRINT 'Fully Approved!';
END

Step 18 – Display Final Results

ShowResult:

SELECT * FROM @ApproverAR
SELECT * FROM @TravelRequest

Example Workflow Output

Level	Approver	Status
1	Ravi	Approved
2	Auto	Auto Approved
3	Auto	Auto Approved
4	Sunita	Approved
5	Rakesh	Pending

Advantages of This Workflow Logic

✔ Handles dynamic approval levels
✔ Prevents workflow deadlock
✔ Skips invalid approvers automatically
✔ Supports duplicate approvers
✔ Maintains complete approval history

Real World Applications

This workflow design can be used in:

Travel Request Approval
Leave Approval System
Expense Approval
Purchase Approval
HRMS Systems
Document Approval Workflow

If you want, I can also write another very powerful blog:

"How to Build a Dynamic Approval Workflow Engine in SQL Server Using Stored Procedures"

which includes:

Production-level design
Dynamic workflow tables
Performance optimization
Transaction handling
Indexing strategy
Enterprise architecture.

Below is the updated blog section you can add after the workflow logic. This explains how to fetch pending approvals for a login user using a stored procedure.

DECLARE @Employees TABLE (
    EmployeeID   INT,
    EmployeeName VARCHAR(50)
);
INSERT INTO @Employees VALUES
(1, 'Ravi Kumar'),
(2, 'Amit Sharma'),
(3, 'Sunita Verma'),   -- DeptHead AND BranchHead (same person)
(4, 'Rakesh Gupta');

DECLARE @WorkFlow TABLE (
    WorkFlowID    INT,
    ReviewLevel   INT,
    ApproverEmpID INT
);
INSERT INTO @WorkFlow VALUES
(1, 1, 1),   -- Level 1 → Amit        (valid)
(2, 2, 0),   -- Level 2 → Not set     (invalid)
(3, 3, 1),   -- Level 3 → Requester   (invalid)
(4, 4, 3),   -- Level 4 → Sunita      (DeptHead)   ← first baar
(5, 5, 4),   -- Level 5 → Rakesh      (BranchAuth)
(6, 6, 3);   -- Level 6 → Sunita      (BranchHead) ← same emp, doosri baar

DECLARE @TravelRequest TABLE (
    TravelRequestID INT,
    EmployeeID      INT,
    TripPurpose     VARCHAR(100),
    Status          INT
);
INSERT INTO @TravelRequest VALUES
(94, 1, 'Client Visit Mumbai', 1);

DECLARE @ApproverAR TABLE (
    ARID            INT IDENTITY(1,1),
    TravelRequestID INT,
    WorkFlowID      INT,
    ReviewLevel     INT,
    ApproverEmpID   INT,
    ARStatus        INT,
    Remarks         VARCHAR(200),
    ARDate          DATETIME
);

-- =============================================
-- VARIABLES
-- =============================================
DECLARE @TravelRequestID INT = 94;
DECLARE @LoginUserEmpID  INT = 2;       -- Sunita login
DECLARE @ActionStatus    INT = 2;
DECLARE @Remarks         VARCHAR(200) = 'Approved by Sunita';

DECLARE @RequesterEmpID  INT;
DECLARE @MaxLevel        INT;
DECLARE @LoginUserLevel  INT;
DECLARE @LoginUserWFID   INT;

-- =============================================
-- SETUP
-- =============================================
SELECT @RequesterEmpID = EmployeeID
FROM @TravelRequest
WHERE TravelRequestID = @TravelRequestID;

SELECT @MaxLevel = MAX(ReviewLevel) FROM @WorkFlow;

-- =============================================
-- FIX: Last processed level ke baad ka
--      login user ka MINIMUM level lo
--      (same employee multiple levels pe ho toh bhi sahi kaam karega)
-- =============================================
DECLARE @LastProcessedLevel INT;

SELECT @LastProcessedLevel = ISNULL(MAX(ReviewLevel), 0)
FROM @ApproverAR
WHERE TravelRequestID = @TravelRequestID;

SELECT TOP 1
    @LoginUserLevel = ReviewLevel,
    @LoginUserWFID  = WorkFlowID
FROM @WorkFlow
WHERE ApproverEmpID = @LoginUserEmpID
  AND ReviewLevel   > @LastProcessedLevel   -- ← sirf unprocessed levels mein dhundo
ORDER BY ReviewLevel ASC;                   -- ← sabse pehla wala lo



SELECT @LoginUserLevel AS LoginUserLevel, @LoginUserWFID AS LoginUserWFID;

-- =============================================
-- STEP 1: Login user ka level INSERT
-- =============================================
INSERT INTO @ApproverAR
    (TravelRequestID, WorkFlowID, ReviewLevel, ApproverEmpID, ARStatus, Remarks, ARDate)
VALUES
    (@TravelRequestID, @LoginUserWFID, @LoginUserLevel, @LoginUserEmpID, @ActionStatus, @Remarks, GETDATE());

-- =============================================
-- STEP 2: Reject → khatam
-- =============================================
IF @ActionStatus = 3
BEGIN
    UPDATE @TravelRequest
    SET Status = 3
    WHERE TravelRequestID = @TravelRequestID;
    GOTO ShowResult;
END

-- =============================================
-- STEP 3: Approve → invalid levels auto insert
-- valid pe ruko
-- =============================================
DECLARE @NextLevel      INT = @LoginUserLevel + 1;
DECLARE @NeedHuman      BIT = 0;
DECLARE @NextValidEmpID INT = 0;
DECLARE @NextValidLevel INT = 0;
DECLARE @NextValidWFID  INT = 0;
DECLARE @NxtEmpID       INT;
DECLARE @NxtWFID        INT;
DECLARE @ApproverType   VARCHAR(20);

WHILE @NextLevel <= @MaxLevel AND @NeedHuman = 0
BEGIN
    SELECT
        @NxtEmpID = ApproverEmpID,
        @NxtWFID  = WorkFlowID
    FROM @WorkFlow
    WHERE ReviewLevel = @NextLevel;

    SET @ApproverType = CASE
        WHEN ISNULL(@NxtEmpID, 0) = 0    THEN 'NOT_SET'
        WHEN @NxtEmpID = @RequesterEmpID  THEN 'REQUESTER'
        ELSE                                   'VALID'
    END;

    IF @ApproverType = 'VALID'
    BEGIN
        -- Valid mila → INSERT NAHI, bas capture
        SET @NextValidLevel = @NextLevel;
        SET @NextValidEmpID = @NxtEmpID;
        SET @NextValidWFID  = @NxtWFID;
        SET @NeedHuman      = 1;
    END
    ELSE
    BEGIN
        -- Invalid → auto insert
        INSERT INTO @ApproverAR
            (TravelRequestID, WorkFlowID, ReviewLevel, ApproverEmpID, ARStatus, Remarks, ARDate)
        VALUES (
            @TravelRequestID,
            @NxtWFID,
            @NextLevel,
            ISNULL(@NxtEmpID, 0),
            2,
            CASE @ApproverType
                WHEN 'NOT_SET'   THEN 'Auto Approved (Approver not set)'
                WHEN 'REQUESTER' THEN 'Auto Approved (Requester is Approver)'
            END,
            GETDATE()
        );
        SET @NextLevel = @NextLevel + 1;
    END
END

-- =============================================
-- STEP 4: Sab khatam → Full Approved
-- =============================================
IF @NeedHuman = 0
BEGIN
    UPDATE @TravelRequest SET Status = 2
    WHERE TravelRequestID = @TravelRequestID;
    PRINT 'Fully Approved!';
END

-- =============================================
-- SHOW RESULT
-- =============================================
ShowResult:

SELECT * FROM @ApproverAR
SELECT * FROM @TravelRequest

-- =============================================
-- DUMMY TABLES
-- =============================================
DECLARE @Employees TABLE (
    EmployeeID   INT,
    EmployeeName VARCHAR(50)
);
INSERT INTO @Employees VALUES
(1, 'Ravi Kumar'),
(2, 'Amit Sharma'),
(3, 'Sunita Verma'),
(4, 'Rakesh Gupta');

DECLARE @WorkFlow TABLE (
    WorkFlowID    INT,
    ReviewLevel   INT,
    ApproverEmpID INT
);
INSERT INTO @WorkFlow VALUES
(1, 1, 2),   -- Level 1 → Amit      (valid)
(2, 2, 0),   -- Level 2 → Not set   (invalid)
(3, 3, 1),   -- Level 3 → Requester (invalid)
(4, 4, 3),   -- Level 4 → Sunita    (valid) ← first baar
(5, 5, 4),   -- Level 5 → Rakesh    (valid)
(6, 6, 3);   -- Level 6 → Sunita    (valid) ← same emp dobara

DECLARE @TravelRequest TABLE (
    TravelRequestID INT,
    EmployeeID      INT,
    TripPurpose     VARCHAR(100),
    Status          INT
);
INSERT INTO @TravelRequest VALUES
(94, 1, 'Client Visit Mumbai', 1);

DECLARE @ApproverAR TABLE (
    ARID            INT IDENTITY(1,1),
    TravelRequestID INT,
    WorkFlowID      INT,
    ReviewLevel     INT,
    ApproverEmpID   INT,
    ARStatus        INT,
    Remarks         VARCHAR(200),
    ARDate          DATETIME
);

-- =============================================
-- VARIABLES
-- =============================================
DECLARE @TravelRequestID INT = 94;
DECLARE @LoginUserEmpID  INT = 2;       -- Amit login
DECLARE @ActionStatus    INT = 2;       -- 2=Approve, 3=Reject
DECLARE @Remarks         VARCHAR(200) = 'Approved by Amit';

DECLARE @RequesterEmpID      INT;
DECLARE @MaxLevel            INT;
DECLARE @LastProcessedLevel  INT;
DECLARE @LoginUserLevel      INT;
DECLARE @LoginUserWFID       INT;

-- =============================================
-- SETUP
-- =============================================
SELECT @RequesterEmpID = EmployeeID
FROM @TravelRequest
WHERE TravelRequestID = @TravelRequestID;

SELECT @MaxLevel = MAX(ReviewLevel) FROM @WorkFlow;

-- Last processed level
SELECT @LastProcessedLevel = ISNULL(MAX(ReviewLevel), 0)
FROM @ApproverAR
WHERE TravelRequestID = @TravelRequestID;

-- Login user ka next unprocessed level
SELECT TOP 1
    @LoginUserLevel = ReviewLevel,
    @LoginUserWFID  = WorkFlowID
FROM @WorkFlow
WHERE ApproverEmpID = @LoginUserEmpID
  AND ReviewLevel   > @LastProcessedLevel
ORDER BY ReviewLevel ASC;


SELECT @LoginUserLevel AS LoginUserLevel, @LoginUserWFID AS LoginUserWFID;

-- =============================================
-- STEP 1: Login user ka level INSERT karo
-- =============================================
INSERT INTO @ApproverAR
    (TravelRequestID, WorkFlowID, ReviewLevel, ApproverEmpID, ARStatus, Remarks, ARDate)
VALUES
    (@TravelRequestID, @LoginUserWFID, @LoginUserLevel, @LoginUserEmpID, @ActionStatus, @Remarks, GETDATE());

-- =============================================
-- STEP 2: Reject → khatam
-- =============================================
IF @ActionStatus = 3
BEGIN
    UPDATE @TravelRequest SET Status = 3
    WHERE TravelRequestID = @TravelRequestID;
    PRINT '✗ Rejected. Workflow band.';
    GOTO ShowResult;
END

-- =============================================
-- STEP 3: CTE — WHILE loop replace
-- Login user ke baad ke levels check karo
-- Invalid → INSERT (auto approved)
-- Pehla Valid → INSERT NAHI (next pending)
-- =============================================
;WITH

-- Sirf login user ke baad wale levels
CTE_WF AS (
    SELECT *
    FROM @WorkFlow
    WHERE ReviewLevel > @LoginUserLevel
),

-- Har level ko valid/invalid mark karo
ARCheck AS (
    SELECT *,
        CASE
            WHEN ISNULL(ApproverEmpID, 0) = 0
              OR ApproverEmpID = @RequesterEmpID
            THEN 0   -- invalid (auto approve)
            ELSE 1   -- valid (human approver)
        END AS IsValidApprover
    FROM CTE_WF
),

-- StopFlag: running sum
-- StopFlag = 0 → sab invalid levels (auto insert hone chahiye)
-- StopFlag = 1 → pehla valid level (INSERT NAHI hoga — next pending)
-- StopFlag > 1 → baad ke levels (abhi kaam nahi)
ARStop AS (
    SELECT *,
        SUM(IsValidApprover) OVER (ORDER BY ReviewLevel) AS StopFlag
    FROM ARCheck
)

-- Sirf auto approve wale insert karo (StopFlag = 0)
-- Valid wala (StopFlag = 1) INSERT NAHI hoga
INSERT INTO @ApproverAR
    (TravelRequestID, WorkFlowID, ReviewLevel, ApproverEmpID, ARStatus, Remarks, ARDate)
SELECT
    @TravelRequestID,
    WorkFlowID,
    ReviewLevel,
    ISNULL(ApproverEmpID, 0),
    2,   -- auto approved
    CASE
        WHEN ISNULL(ApproverEmpID, 0) = 0  THEN 'Auto Approved (Approver not set)'
        WHEN ApproverEmpID = @RequesterEmpID THEN 'Auto Approved (Requester is Approver)'
    END,
    GETDATE()
FROM ARStop
WHERE StopFlag = 0;   -- ← sirf invalid levels, valid wala nahi

-- =============================================
-- STEP 4: Sab khatam? → Full Approved
-- Valid approver koi bacha hi nahi
-- =============================================
IF NOT EXISTS (
    SELECT 1 FROM @WorkFlow
    WHERE ReviewLevel   > @LoginUserLevel
      AND ISNULL(ApproverEmpID, 0) <> 0
      AND ApproverEmpID <> @RequesterEmpID
)
BEGIN
    UPDATE @TravelRequest SET Status = 2
    WHERE TravelRequestID = @TravelRequestID;
    PRINT '✓✓ Fully Approved!';
END

-- =============================================
-- SHOW RESULT
-- =============================================
ShowResult:

SELECT * FROM @ApproverAR

SQL Server – Getting Pending Approvals for Logged-in User

After building the approval workflow engine, the next important requirement in any system is:

👉 Show pending approvals for the logged-in user

For example:

Sunita logs into the system
The system should show only those requests where Sunita is the next approver

This is important because:

✔ Users should only see approvals assigned to them
✔ Requests already processed by them should not appear again
✔ Only the next workflow level should be visible

To implement this we can create a stored procedure.

Stored Procedure – Get Pending Approvals

Below is the complete stored procedure.

CREATE OR ALTER PROCEDURE usp_GetPendingApprovals_Test
    @LoginUserEmpID INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Employees TABLE (EmployeeID INT, EmployeeName VARCHAR(50));
    INSERT INTO @Employees VALUES (1,'Ravi'),(2,'Amit'),(3,'Sunita'),(4,'Rakesh');
    DECLARE @WorkFlow TABLE (WorkFlowID INT, ReviewLevel INT, ApproverEmpID INT);
    INSERT INTO @WorkFlow VALUES (1,1,2),(2,2,0),(3,3,1),(4,4,3),(5,5,4),(6,6,3);
    DECLARE @TravelRequest TABLE (
        TravelRequestID INT,
        EmployeeID INT,
        TripPurpose VARCHAR(100),
        Status INT,
        SubmittedDate DATETIME
    );
    INSERT INTO @TravelRequest VALUES
    (94,1,'Client Visit Mumbai',1,GETDATE());
    DECLARE @ApproverAR TABLE (
        ARID INT IDENTITY,
        TravelRequestID INT,
        WorkFlowID INT,
        ReviewLevel INT,
        ApproverEmpID INT,
        ARStatus INT,
        Remarks VARCHAR(200),
        ARDate DATETIME
    );
    -- Already processed approvals
    INSERT INTO @ApproverAR VALUES (94,1,1,2,2,'Approved',GETDATE());
    INSERT INTO @ApproverAR VALUES (94,2,2,0,2,'Auto',GETDATE());
    INSERT INTO @ApproverAR VALUES (94,3,3,1,2,'Auto',GETDATE());
    INSERT INTO @ApproverAR VALUES (94,4,4,3,2,'Approved',GETDATE());
    INSERT INTO @ApproverAR VALUES (94,5,5,4,2,'Approved',GETDATE());
    --SELECT * FROM @WorkFlow
    --SELECT * FROM @ApproverAR
    SELECT
        TR.TravelRequestID,
        TR.TripPurpose,
        E.EmployeeName                                      AS RequesterName,
        WF.ReviewLevel                                      AS PendingOnLevel,
        (SELECT MAX(ReviewLevel) FROM @WorkFlow)            AS TotalLevels,
        (
            SELECT COUNT(*)
            FROM @ApproverAR AR2
            WHERE AR2.TravelRequestID = TR.TravelRequestID
        )                                                   AS CompletedLevels,
        (
            SELECT MAX(AR3.ARDate)
            FROM @ApproverAR AR3
            WHERE AR3.TravelRequestID = TR.TravelRequestID
        )                                                   AS LastActionDate,
        TR.SubmittedDate
    FROM @TravelRequest TR
    INNER JOIN @Employees E
        ON E.EmployeeID = TR.EmployeeID
    INNER JOIN @WorkFlow WF
        ON WF.ApproverEmpID = @LoginUserEmpID
    WHERE TR.Status = 1
    AND WF.ReviewLevel =
    (
        SELECT MIN(WF_Next.ReviewLevel)
        FROM @WorkFlow WF_Next
        WHERE WF_Next.ReviewLevel >
        (
            SELECT ISNULL(MAX(AR.ReviewLevel),0)
            FROM @ApproverAR AR
            WHERE AR.TravelRequestID = TR.TravelRequestID
        )
    )
    AND NOT EXISTS
    (
        SELECT 1
        FROM @ApproverAR AR_Done
        WHERE AR_Done.TravelRequestID = TR.TravelRequestID
          AND AR_Done.ApproverEmpID   = @LoginUserEmpID
          AND AR_Done.ReviewLevel     = WF.ReviewLevel
          AND AR_Done.ARStatus        IN (2,3)
    )
    AND WF.ApproverEmpID = @LoginUserEmpID
    ORDER BY TR.SubmittedDate;
END
GO


EXEC usp_GetPendingApprovals_Test 3

Executing the Procedure

Now we execute the procedure for Sunita.

EXEC usp_GetPendingApprovals_Test @LoginUserEmpID = 3;

This will return only those requests where Sunita is the next approver.

How the Logic Works

1️⃣ Find Last Processed Level

SELECT MAX(ReviewLevel)
FROM @ApproverAR
WHERE TravelRequestID = TR.TravelRequestID

This identifies the last approval level completed.

2️⃣ Find Next Workflow Level

SELECT MIN(WF_Next.ReviewLevel)
FROM @WorkFlow WF_Next
WHERE WF_Next.ReviewLevel > LastProcessedLevel

This gives the next pending level.

3️⃣ Match Login User

WF.ApproverEmpID = @LoginUserEmpID

Ensures the logged-in user is the correct approver.

4️⃣ Prevent Duplicate Approval

NOT EXISTS

This ensures the user has not already approved that level.

Example Output

TravelRequestID	Requester	Pending Level	Completed Levels
94	Ravi	6	5

Meaning:

✔ Levels 1-5 already processed
✔ Level 6 pending for Sunita

Advantages of This Procedure

✔ Shows only relevant approvals
✔ Prevents duplicate approvals
✔ Works with dynamic workflow levels
✔ Handles auto-approved levels correctly
✔ Supports multiple approval roles for same employee

Real Production Use

This stored procedure can be used in:

HRMS approval dashboards
Manager approval inbox
Travel approval screens
Expense approval systems

Frontend (Angular / React / Power BI / .NET) can directly call this procedure to display pending approvals.

If you want, I can also show you a very powerful enterprise design next:

“How to build a fully dynamic Approval Workflow Engine in SQL Server (Production Architecture)”

It will include:

Workflow master tables
Role based approvers
Parallel approvals
Delegation logic
Escalation logic
Performance indexing.

Topics covered

Multi-Level Approval Workflow SQL

Keep building your data skillset

Explore more SQL, Python, analytics, and engineering tutorials.

Browse All Posts