Is it possible to use Scalar-valued Functions in IF-statement?-CodePudding

So we have this SQL-query that we use in very many places that I would like to simplify by putting it in a function so that we don't have to write the query every single time. But when replacing the uses I stumbled upon an issue where we sometimes would use it inside an IF-statement. Is there any way to use this as a function? Or maybe move it to a stored procedure instead?

Function:

ALTER FUNCTION [dbo].[fn_GetOption]
(
    -- Add the parameters for the function here
    @id int,
    @searchKey nvarchar(32) = NULL
)
RETURNS nvarchar(128)
AS
BEGIN
    
    DECLARE @Result nvarchar(128)
    
    IF @searchKey IS NULL
    BEGIN
        SELECT @Result = [VALUE]
        FROM [OPTIONS]
        WHERE [ID] = @id
    END
    ELSE
    BEGIN
        SELECT @result = ISNULL(eoo.[VALUE], eo.[VALUE])
        FROM 
            [OPTIONS] eo
            LEFT OUTER JOIN [OPTIONS_OVERRIDE] eoo ON eo.[ID] = eoo.[ID] AND eoo.[SEARCH_KEY] = @searchKey
        WHERE 
            eo.[ID] = @id 
    END

    RETURN @Result

END

Usage: (not working)

IF 'Y' = EXEC [fn_GetOption] 69, 'myKey'
BEGIN
    PRINT '1'
END

CodePudding user response：

Remove the EXEC and add brackets:

IF 'Y' = dbo.[fn_GetOption] (69, 'myKey')
BEGIN
    PRINT '1'
END