|
|
Error Message
Server: Msg 4511, Level 16, State 1, Procedure <View or
Function Name>, Line 1
Create View or Function failed because no column name
was specified for column <Column Number>
Causes
A view is a virtual table whose contents are defined by a query. Like a real table, a view consists of a set of named columns and rows of data. However, a view does not exist as a stored set of data values in a database. The rows and columns of data come from one or more tables referenced in the query defining the view and are produced dynamically when the view is referenced.
|
Views are created using the CREATE VIEW statement. The basic syntax of the CREATE VIEW is as follows:
CREATE VIEW [ <schema_name>. ] <view_name> [ ( column [, …n ] ) ]
[ WITH { [ENCRYPTION], [SCHEMABINDING], [VIEW_METADATA] } ]
AS <select_statement>
[ WITH CHECK OPTION ]
Just like in a table, one of the restrictions when creating a view is that the names of the columns must be specified; otherwise this error message will be raised. Here’s a script that shows a view definition that generates this error message:
CREATE TABLE [dbo].[Employee] (
[EmployeeID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50)
)
GO
CREATE VIEW [dbo].[EmployeeView]
AS
SELECT [EmployeeID], [FirstName], [LastName],
[LastName] + ', ' + [FirstName]
FROM [dbo].[Employee]
GO
Since there’s no column name assigned to the concatenation of the [LastName] and the [FirstName] in the SELECT statement of the view, the following error message is raised by the CREATE VIEW statement:
Msg 4511, Level 16, State 1, Procedure EmployeeView, Line 3
Create View or Function failed because no column name was specified for column 4.
Solution / Work Around:
This error can easily be avoided by making sure that all columns returned by a view definition have assigned column names. Here’s an updated version of the view earlier that avoids this error message:
CREATE VIEW [dbo].[EmployeeView]
AS
SELECT [EmployeeID], [FirstName], [LastName],
[LastName] + ', ' + [FirstName] AS [FullName]
FROM [dbo].[Employee]
GO
As an alternative, the second way of creating a view can be used wherein the column names used by the view are included in the CREATE VIEW statement, as can be seen in the following:
CREATE VIEW [dbo].[EmployeeView]
( [EmployeeID], [FirstName], [LastName], [FullName] )
AS
SELECT [EmployeeID], [FirstName], [LastName],
[LastName] + ', ' + [FirstName]
FROM [dbo].[Employee]
GO
|