|
|
Error Message:
Server: Msg 444, Level 16, State 2, Procedure , Line 9
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 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/Workaround:
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.
|