Display message based on column value returned from stored procedure

Question

I want to create a stored procedure that returns different messages based on the data in the table. Here's what my current stored procedure looks like:

ALTER PROCEDURE [dbo].[sp_GETLIST_ACC]
    @per_id int
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @status varchar(10)
    
    SELECT @status = req_status 
    FROM TOL_FILE_REQUEST 
    WHERE per_id = @per_id

    IF @status = 'A'
    BEGIN
        SET @status = 'Approved'
    END
    ELSE IF @status = 'P'
    BEGIN
        SET @status = 'Pending'
    END

    SELECT 
        req_id,
        file_no,
        req_status,
    FROM 
        TOL_FILE_REQUEST
    WHERE 
        per_id = @per_id
END

The table is this

req_id | file_no  | req_status
-------+----------+-----------
  6    | AS000001 |    A
 10    | AS000002 |    P
 11    | AS000003 |    A
 12    | AS000004 |    A
 13    | AS000005 |    A

But after executing the stored procedure, the following is what I got:

req_id | file_no  | req_status
-------+----------+-----------
  6    | AS000001 | Approved    
 10    | AS000002 | Approved    
 11    | AS000003 | Approved
 12    | AS000004 | Approved
 13    | AS000005 | Approved    

but what I need is this:

req_id | file_no  | req_status
-------+----------+-----------
  6    | AS000001 | Approved    
 10    | AS000002 | Pending
 11    | AS000003 | Approved
 12    | AS000004 | Approved
 13    | AS000005 | Approved    

From my understanding, the req_status follows the latest inserted data. but why? And what can I do to overcome this? I tried iterating using cursor and using a counter but the it exec multiple time instead.

Thanks in advance!

Answer 1

Just do it all in one statement with a CASE statement.

ALTER PROCEDURE [dbo].[sp_GETLIST_ACC]
    @per_id int

AS
BEGIN

    SET NOCOUNT ON;
    SELECT req_id,
           file_no,
           CASE req_status
               WHEN 'A' THEN 'Approved'
               WHEN 'P' THEN 'Pending'
               ELSE 'Unknown'
           END as req_status
    FROM TOL_FILE_REQUEST
    WHERE per_id = @per_id
END