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

Question

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

Your error is not related to CASE. It is related to your function declaration and how it attempts to gather and return the appropriate value(s). Post the complete function declaration and someone can give you guidance. Now is a good time to go read the documentation and to educate yourself on how and when to use user-defined functions. — SMor, Nov 11 '20 at 13:42
thank you, but I don't want to parse the location code. I want it to query the operationhours table. I am trying to use the following script stackoverflow.com/questions/5274208/… I want to only parse the start and end datetime. therefore I thought using case would work for me. each site opens and closes at different times which is in the table operationhours. right now start time and finish time are "static" at 9 - 5. I want it based per site. I hope I make some sense — Antz, Nov 11 '20 at 14:04

score 2 · Accepted Answer · answered Nov 11 '20 at 12:39

2

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

answered Nov 11 '20 at 12:39

NILF

367
2
7

thank you, but I don't want to parse the location code. I want it to query the operationhours table. I am trying to use the following script https://stackoverflow.com/questions/5274208/calculate-business-hours-between-two-dates I want to only parse the start and end datetime. therefore I thought using case would work for me. each site opens and closes at different times which is in the table operationhours. right now start time and finish time are "static" at 9 - 5. I want it based per site. I hope I make some sense – Antz Nov 11 '20 at 13:41

score 1 · Answer 2 · answered Nov 11 '20 at 15:58

"Select statements included within a function cannot return data to a client"

To clarify the error message: you must assign your result to a variable and return the variable. A SELECT as shown in your question, will return a dataset to the client. As noted in the error message, that is not allowed for a function. Returning a dataset works for a stored procedure, but not a function.

Fix: Assign the result of your SELECT to a variable.

The answer by @Illia above should work.

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

2 Answers2