|
|
Error Message
Server: Msg 4514, Level 16, State 1, Procedure <View or
Function Name>, Line 1
CREATE FUNCTION failed because a column name is not
specified for column <Column Number>.
Causes
User-defined functions are routines that accept parameters, perform an action such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set.
|
These are the main types of functions in SQL Server:
- Scalar Function
- Inline Table-Valued Function
- Multi-Statement Table-Valued Function
- CLR Scalar Function
- CLR Table-Valued Function
User-defined scalar functions return a single data value of the type defined in the RETURNS clause of the function. User-defined inline table-valued functions return a table data type and does not contain a function body; the table returned by the function is the result set of a single SELECT statement. On the other hand, user-defined multi-statement table-valued functions also return a table data type but contains a function body.
This error message is usually generated by an inline-table-valued function. The syntax of an inline table-valued function is as follows:
CREATE FUNCTION [ <schema_name.> ] <function_name>
( [ { @parameter_name [ AS ] <parameter_data_type> ] )
RETURNS TABLE
AS
RETURN ( <select_statement> )
Here’s a script that shows a user-defined table-valued function that generates this error message:
CREATE TABLE [dbo].[Employee] (
[EmployeeID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
[FirstName] VARCHAR(100),
[LastName] VARCHAR(100)
)
GO
CREATE FUNCTION [dbo].[ufn_EmployeeSearch] (
@FirstName VARCHAR(50),
@LastName VARCHAR(50)
)
RETURNS TABLE
AS
RETURN SELECT [EmployeeID], [FirstName], [LastName], [LastName] + ', ' + [FirstName]
FROM [dbo].[Employee]
WHERE [FirstName] LIKE '%' + @FirstName + '%' AND
[LastName] LIKE '%' + @LastName + '%'
GO
Since there’s no column name assigned to the concatenation of the [LastName] and the [FirstName] in the SELECT statement of the function, the following error message is raised by the CREATE FUNCTION statement:
Msg 4514, Level 16, State 1, Procedure ufn_EmployeeSearch, Line 8
CREATE FUNCTION failed because a column name is not specified for column 4.
Solution / Work Around:
This error can easily be avoided by making sure that all columns returned by a table-valued user-defined function have assigned column names. Here’s an updated version of the user-defined table-valued function earlier that avoids this error message:
CREATE FUNCTION [dbo].[ufn_EmployeeSearch] (
@FirstName VARCHAR(50),
@LastName VARCHAR(50)
)
RETURNS TABLE
AS
RETURN SELECT [EmployeeID], [FirstName], [LastName],
[LastName] + ', ' + [FirstName] AS [FullName]
FROM [dbo].[Employee]
WHERE [FirstName] LIKE '%' + @FirstName + '%' AND
[LastName] LIKE '%' + @LastName + '%'
GO
|