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