SQL Exit scalar function prematurely

In SQL, you can exit the stored procedure prematurely with RETURN statement.

-- CHECK IF IT IS A VALID AND EXISTING JOB NAME
IF NOT EXISTS (SELECT * FROM msdb..sysjobs WHERE name = @job_name)
BEGIN
    RAISERROR ('[ERROR]:[%s] job does not exist. Please check',16, 1, @job_name) WITH LOG
    RETURN
END

However, when I try to apply the same principle to the scalar function, it returns the error, since function needs to return something.

CREATE FUNCTION [dbo].[fn_GetJobStatus]
    (@job_name SYSNAME)
RETURNS INT
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @JobCompletionStatus INT;

    -- CHECK IF IT IS A VALID AND EXISTING JOB NAME
    IF NOT EXISTS (SELECT * FROM msdb..sysjobs WHERE name = @job_name)
    BEGIN
        RAISERROR ('[ERROR]:[%s] job does not exist. Please check',16, 1, @job_name) WITH LOG
        RETURN   <--ERROR HERE
    END
    
    <!--Scalar function logic here -->

    RETURN @JobCompletionStatus;
END

How do I prematurely exit the scalar function the same way RETURN is used in the stored procedure?

Thank you.

Answer

You can’t RAISERROR or THROW in a function either. It sounds like you really want a stored procedure with an output parameter or a resultset.

Advertisement

Answer