Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Differences Between ISNULL and COALESCE Functions
Error Messages
Home > SQL Server Error Messages > Msg 4514 - CREATE FUNCTION failed because a column name is not specified for column <Column Number>.
SQL Server Error Messages - Msg 4514 - CREATE FUNCTION failed because a column name is not specified for column <Column Number>.

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
Related Articles :