Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : How to Call NEWID Function in a User-Defined Function
Error Messages
Home > SQL Server Error Messages > Msg 444 - Select statements included within a function cannot return data to a client.
SQL Server Error Messages - Msg 444 - Select statements included within a function cannot return data to a client.

Error Message

Server: Msg 444, Level 16, State 2, Line 1
Select statements included within a function cannot
return data to a client.

Causes:

As the error message suggests, you are trying to issue a SELECT statement inside a function that will return the result to the caller, which is not allowed inside a function.

To illustrate, let’s say you want to return the details of a particular user given the user name and if the user name does not exist in the database, you want to return an error message.  For this task you create a function similar to the following:

CREATE FUNCTION [dbo].[GetUserInformation] ( @UserName VARCHAR(10) )
RETURNS VARCHAR(100)
AS 
BEGIN
    DECLARE @ErrorMessage    VARCHAR(100)

    SET @ErrorMessage = ''
    IF EXISTS (SELECT 'X' FROM [dbo].[Users] WHERE [UserName] = @UserName)
        SELECT * FROM [dbo].[Users]
        WHERE [UserName] = @UserName
    ELSE
        SET @ErrorMessage = 'Invalid User Name'

    RETURN @ErrorMessage
END
GO

Executing this script in Query Analyzer or Management Studios will generate the following error because SELECT statements inside a function that return data to the function caller is not allowed:

Server: Msg 444, Level 16, State 2, Procedure GetUserInformation, Line 9
Select statements included within a function cannot return data to a client.

Solution / Work Around:

If you need to return result sets to the client, you have to use a stored procedure and not a function for this purpose.

CREATE PROCEDURE [dbo].[GetUserInformation] ( @UserName VARCHAR(10) )
AS 

SELECT * FROM [dbo].[Users]
WHERE [UserName] = @UserName
GO

If you want to return an error message if the user is not found, one way to do this is to return the error message as a separate result set:

CREATE PROCEDURE [dbo].[GetUserInformation] ( @UserName VARCHAR(10) )
AS 
    IF EXISTS (SELECT 'X' FROM [dbo].[Users]
               WHERE [UserName] = @UserName)
    BEGIN
        SELECT '' AS [ErrorMessage]
        SELECT * FROM [dbo].[Users]
        WHERE [UserName] = @UserName
    END
    ELSE
        SELECT 'Invalid User Name' AS [ErrorMessage]
GO

If the user name is found in the Users table, 2 result sets are returned.  The first result set contains the error message, which is an empty string, and the second result set is the user information.  If the user name is not found in the Users table, only 1 result set is returned which contains the error message.

If you don’t want to have 2 separate result sets, you can also just return one result set by including the error message in the user information result set:

CREATE PROCEDURE [dbo].[GetUserInformation] ( @UserName VARCHAR(10) )
AS 
    IF EXISTS (SELECT 'X' FROM [dbo].[Users]
               WHERE [UserName] = @UserName)
        SELECT '' AS [ErrorMessage], [FirstName], [LastName]
        FROM [dbo].[Users]
        WHERE [UserName] = @UserName
    ELSE
        SELECT 'Invalid User Name' AS [ErrorMessage], 
               '' AS [FirstName], '' AS [LastName]
GO

As can be seen from the code, if the user name is found in the Users table, an empty string is returned as the error message together with the user information.  However if the user name is not found in the Users table, the error message is returned together with empty string values for the user information.

Related Articles :