|
|
Error Message
Server: Msg 317, Level 16, State 1, Line 1
Table-valued function '<Function Name>' cannot have
a column alias.
Causes
User-defined functions have a two-part structure: a header and a body. The function can take zero or more input parameters and returns either a scalar value (making it a scalar function) or a table (making it a table-valued function). User-defined scalar functions return a single data value of the type defined in the RETURNS clause of the function definition. On the other hand, user-defined table functions return a table data type.
|
The header of a function defines the name of the function with the optional schema/owner name, the input parameter names and data types, options applicable to the input parameters, return parameter data type and optional name, and options applicable to the return parameter. The body of a function, which defines the action or logic that the function will perform, contains either one or more Transact-SQL statements that perform the function logic, or a reference to a .NET assembly.
There are two types of user-defined table functions, namely, the inline table-valued function and the multi-statement table-valued function. An inline table-valued function does not have a function body but only contains a single SELECT statement to return the result set. On the other hand, a multi-statement table-valued function contains a function body defined within a BEGIN … END block and contains a series of Transact-SQL statements that build and insert rows into the table that will be returned as a result set.
When creating a table-valued function, either a multi-statement or an inline/scalar, each column must be assigned a name. And when calling the table-valued function, aliases are not allowed, otherwise this error message is raised.
To illustrate, here’s a script that creates a [dbo].[Product] table as well as a table-valued function that performs a search on the [dbo].[Product] table for the given product name and returns the result as a table:
CREATE TABLE [dbo].[Product] (
[ProductID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
[ProductName] NVARCHAR(100),
[UnitPrice] MONEY,
[Quantity] INT
)
GO
CREATE FUNCTION [dbo].[ufn_ProductSearch] ( @ProductName NVARCHAR(50) )
RETURNS TABLE AS
RETURN ( SELECT [ProductID], [ProductName], [UnitPrice], [Quantity]
FROM [dbo].[Product]
WHERE [ProductName] LIKE '%' + @ProductName + '%' )
GO
Calling the function and assigning an alias to each output column will generate this error message, as can be seen in the following SELECT statement:
SELECT *
FROM [dbo].[ufn_ProductSearch] ( 'Soap' ) [Prod] ( [ID], [Name], [Price], [Qty] )
GO
Msg 317, Level 16, State 1, Line 2
Table-valued function 'ufn_ProductSearch' cannot have a column alias.
Solution / Work Around:
This error message can easily be avoided by simply not including the column aliases when calling the table-valued function:
SELECT *
FROM [dbo].[ufn_ProductSearch] ( 'Soap' ) [Prod]
GO
If the purpose of including the column alias is to rename the column names, then it can be done from the SELECT clause, as can be seen from the following:
SELECT [ProductID] AS [ID], [ProductName] AS [Name], [UnitPrice] AS [Price], [Quantity] AS [Qty]
FROM [dbo].[ufn_ProductSearch] ( 'Soap' ) [Prod]
GO
|