I am unsure if there is a way to handle for multiple return statuses

IF @Age NOT BETWEEN 22 and 65
    RETURN -101 -- Age Criteria not met
ELSE IF (SELECT mem_status FROM Member WHERE mem_nric=@MemberNRIC) <>'Active'
    RETURN -102 -- Member Status inelgible
ELSE IF @StartDate<dateadd(DAY, 1, getdate())
    RETURN -103 --Reservation date before next 24 hours
ELSE IF @StartDate>dateadd(DAY, 7, getdate())
    RETURN -104
ELSE IF DATEPART(HOUR,@StartDate)<7
    RETURN -105 --Booking before 7am

I want to know if there is way I can set these multiple returns to return all at once or something because it is kind of inefficient to check for one error and then return when there can be multiple errors, which would mean each error would only surface after the previous is corrected.

CodePudding user response：

If you are stuck using return codes due to a requirements limitation, then this answer will not help you in returning multiple errors.

As stated in the comments discussion, a stored procedure cannot return multiple return codes, it can only return a single integer.

Here's a reference to the documentation on the subject:

https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure?view=sql-server-ver15#returning-data-using-a-return-code

However, what you can do, is return multiple error messages, and you can return a bad return code if any errors have been detected. There is likely a cleaner way to do this, but this is one way it could be done:

CREATE PROCEDURE dbo.usp_StackOverflow_Answer (
    @Age tinyint,
    @StartDate datetime,
    @MemberNRIC int
)
AS
BEGIN
    DECLARE @errorStatus bit = 0;

    IF (@Age NOT BETWEEN 22 and 65)
    BEGIN;
        RAISERROR('Age Criteria not met',16,1) WITH NOWAIT;
        SET @errorStatus = 1;
    END;

    IF ((SELECT mem_status FROM Member WHERE mem_nric = @MemberNRIC) <> 'Active')
    BEGIN;
        RAISERROR('Member Status inelgible',16,1) WITH NOWAIT;
        SET @errorStatus = 1;
    END;

    IF (@StartDate < DATEADD(DAY, 1, GETDATE()))
    BEGIN;
        RAISERROR('Reservation date before next 24 hours',16,1) WITH NOWAIT;
        SET @errorStatus = 1;
    END;

    IF (@StartDate > DATEADD(DAY, 7, GETDATE()))
    BEGIN;
        RAISERROR('Reservation date more than 7 days in future',16,1) WITH NOWAIT;
        SET @errorStatus = 1;
    END;

    IF (DATEPART(HOUR, @StartDate) < 7)
    BEGIN;
        RAISERROR('Booking before 7am',16,1) WITH NOWAIT;
        SET @errorStatus = 1;
    END;

    IF (@errorStatus = 1)
    BEGIN;
        RETURN -1;
    END;
END;
GO

DECLARE @returnCode int;
EXEC @returnCode = dbo.usp_StackOverflow_Answer @Age = 21, @StartDate = '2021-11-30', @MemberNRIC = 0;
SELECT @returnCode;

Reasons for using RAISERROR in this scenario as opposed to other options:

• PRINT - does not return error codes only messages
• TRY/CATCH - will not work here because we want to collect multiple errors, and then return them all. In this instance we are using severity level 16, and anything that is 11 will transfer execution to the CATCH block, which is not what we want.
• THROW - while this command does use the same severity level of 16, it does stop the execution of the stored procedure. So the issue is similar to using a TRY/CATCH block.