Error – Select statements included within a function cannot return data to a client

I am trying to create a function that will query times in a table and then return the correct time based on the site.

The times in the table being queried have the opening and closing times of the branch. I am not sure if I should be using CASE or IF.

When I use CASE, I get this error:

Msg 444, Level 16, State 2, Procedure WorkTime, Line 33
Select statements included within a function cannot return data to a client.

When I print to screen it works fine.

/added this for the site times open and close/

DECLARE @WorkStart TIME
SET @WorkStart = '09:00'  

SELECT
    CASE    
        WHEN [Location Code] = 'OF' THEN [OpenTime]
        WHEN [Location Code] = 'MP' THEN [OpenTime]
        WHEN [Location Code] = 'GP' THEN [OpenTime]
        WHEN [Location Code] = 'EC' THEN [OpenTime]
        WHEN [Location Code] = 'WP' THEN [OpenTime]
        WHEN [Location Code] = 'ZN' THEN [OpenTime]
    END
FROM 
    [dbo].[OperationHours]

--PRINT @WorkStart
--GO

Answer

First of all I think you didn’t specify all requirements. I expect that you pass the location code to the function.

your function could have a look like

CREATE FUNCTION YourFunction
(
    @locationCode varchar(2)
)
RETURNS TIME
AS
BEGIN
    DECLARE @OpenTime TIME
    DECLARE @WorkStart TIME
    SET @WorkStart = '09:00'  

    SELECT @OpenTime = [OpenTime]
    FROM [dbo].[OperationHours]
    where @locationCode = [Location Code]

    RETURN ISNULL(@OpenTime, @WorkStart)
END