Inline Function, Column names in each view or function must be unique

Question

I have join the two table and now i want to save that statement in function so that I can use that again and again. I tried to create the function as follow:-

create function fn_electricalsem1and2()
returns table
as
return (
      Select * 
      from [Electrical Semester 1 Regular] as T1 
      inner join[Electrical Semester 2 Regular] As T2 On T1.department_id= T2.department_id
)

Now I am getting error

Column names in each view or function must be unique. Column name 'Name' in view or function 'fn_electricalsem1and2' is specified more than once.

I haven't made any function till now but why am I getting error function must be unique?

[Bad habits to kick : using SELECT * / omitting the column list](https://sqlblog.org/2009/10/10/bad-habits-to-kick-using-select-omitting-the-column-list) - As an aside, if this function is going to be parameterless then it may make more sense as a view rather than a function. Either way though, you will need to be explicit about the columns you want to return rather than using `SELECT *`. — GarethD, Feb 26 '20 at 12:56
A few more bad habits that you may wish to kick - [using dashes and spaces in entity names](https://sqlblog.org/2009/10/09/bad-habits-to-kick-using-dashes-and-spaces-in-entity-names) - [using table aliases like (a, b, c) or (t1, t2, t3)](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3), [avoiding the schema prefix](https://sqlblog.org/2019/09/12/bad-habits-to-kick-avoiding-the-schema-prefix) — GarethD, Feb 26 '20 at 13:49

Suraj Kumar · Accepted Answer · 2020-02-26T13:19:43.413

1

It is giving error because your function is returning a table and in a table all the column name should be unique.

To solve the issue you need to replace

Select *

To

Select t1.column1 as Col1, t1.column2, t2.column1 as Col2, ...and so on

In your case department_id is available in both the tables. Also both table contains a column of name: name.

edited Feb 26 '20 at 13:19

answered Feb 26 '20 at 12:50

Suraj Kumar

5,547
8
20
42

My Select statement is correct and it works too but my main problem is I can't create the function – Awesome Feb 26 '20 at 12:55
@Awesome I am saying that use alias and the column name while creating the function as t1.name as t1name, t2.name as t2Name, etc. It is giving error because function is returning a table and in the table all column name should be unique. – Suraj Kumar Feb 26 '20 at 12:56
@Awesome I think the select statement returns the result set not the table so in select it does not give the error even if more than one column with the same name. – Suraj Kumar Feb 26 '20 at 12:59

score 0 · Answer 2 · answered Feb 26 '20 at 13:36

In the following query, we will create the tables at first and populate them some data.

CREATE TABLE [Electrical Semester 1 Regular](department_id INT);
CREATE TABLE [Electrical Semester 2 Regular](department_id INT);
INSERT INTO [Electrical Semester 1 Regular]
VALUES(1);
INSERT INTO [Electrical Semester 2 Regular]
VALUES(1);

When we execute the following query you will not experience any error ;

 Select * 
 from [Electrical Semester 1 Regular] as T1 
      inner join[Electrical Semester 2 Regular] As T2 On T1.department_id= T2.department_id

+---------------+---------------+
| department_id | department_id |
+---------------+---------------+
|             1 |             1 |
+---------------+---------------+

However, the following query returns an error because of the same column names of the [Electrical Semester 1 Regular] and [Electrical Semester 2 Regular]. In the SQL Server if we want to create a view or function we have to give unique names with alias

create function fn_electricalsem1and2()
returns table
as
return (
      Select * 
      from [Electrical Semester 1 Regular] as T1 
      inner join[Electrical Semester 2 Regular] As T2 On T1.department_id= T2.department_id
)

In the SQL Server if we want to create a view or function we have to give unique names. You enable it with an alias so you can change your query like the below;

create function fn_electricalsem1and2()
returns table
as
return (
      Select t1.department_id as t1_department_id , t2.department_id as t2_department_id
      from [Electrical Semester 1 Regular] as T1 
      inner join[Electrical Semester 2 Regular] As T2 On T1.department_id= T2.department_id
)

GO
select * from fn_electricalsem1and2()


    +------------------+------------------+
    | t1_department_id | t2_department_id |
    +------------------+------------------+
    |                1 |                1 |
    +------------------+------------------+

Inline Function, Column names in each view or function must be unique

2 Answers2