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

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

When 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.

Let's see the below example to understand more.

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

Msg 444, Level 16, State 2, Procedure GetUserInformation, Line 9 [Batch Start Line 0]
Select statements included within a function cannot return data to a client.

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.

The TABLESAMPLE clause can only be used with local tables

Msg 494, Level 16, State 1, Line 1
The TABLESAMPLE clause can only be used with local tables.

The TABLESAMPLE clause cannot be applied to derived tables, tables from linked servers, and tables derived from table-valued functions, rowset functions, or OPENXML. TABLESAMPLE cannot also be specified in the definition of a view or an inline table-valued function.

If the TABLESAMPLE clause is applied to a table-valued function, such as the following SELECT statement, then this error message will be generated:

CREATE FUNCTION [dbo].[ufn_GetCustomersByZIPCode] ( @ZIPCode VARCHAR(5))
RETURNS TABLE
AS
RETURN (SELECT * FROM [dbo].[Customers]
        WHERE [ZIPCode] = @ZIPCode)
GO

SELECT * FROM [dbo].[ufn_GetCustomersByZIPCode] A TABLESAMPLE (100 ROWS)

Msg 494, Level 16, State 1, Line 8
The TABLESAMPLE clause can only be used with local tables.

If the purpose of using the TABLESAMPLE clause is to generate a random set of rows from a table, table-valued function or view, then instead of using the TABLESAMPLE clause, the TOP clause together with the ORDER BY NEWID() clause can be used in its place.

In the case of the inline table-valued function shown earlier, the following SELECT statement can be used in its place which will generate 100 random rows from the rows returned by the inline table-valued function:

SELECT TOP 100 *
FROM [dbo].[ufn_GetCustomersByZIPCode] ( '12345' ) A
ORDER BY NEWID()