T-SQL Function RETURNS @returntable COLUMN ALIAS ignored

Question

With T-SQL this function

CREATE FUNCTION [dbo].[FuncOrder]
(
   @param1 int,
   @param2 int
)
RETURNS @returntable TABLE
(
   c1 int,
   c2 int
)
AS
BEGIN
   INSERT @returntable
   SELECT @param1 as c2, @param2 as c1
   RETURN
END

Executing with this

SELECT * FROM [dbo].[FuncOrder](
1,
2)
GO

Result is this

c1  c2
1   2

I was expecting that the column alias would be matched to the names in the return table definition. It seems to me that the order of the select columns matters and not the column alias. Does anyone have a link to where the documents say that.

When you use a select statement as the source of an insert like that you can't control the order of the columns by assigning the columns an alias. There is nothing in the documentation anywhere because this just isn't how it work. However, your code has some issues that you should sort out. First of all, NEVER assume columns in an inserts statement. You should explicitly name the columns. Also, you have a table valued function here so there really is no need for a table variable. Just return your select statement. — Sean Lange, Apr 18 '16 at 21:46
This is standard `insert into select from` syntax, nothing to do with the table-valued function. E.g. examples [here](https://technet.microsoft.com/en-us/library/ms188263.aspx) show column-name mismatches are ignored, only position is considered. — Blorgbeard, Apr 18 '16 at 21:46
http://stackoverflow.com/questions/9807724/does-insert-into-select-always-match-fields-by-ordinal-position — hcaelxxam, Apr 18 '16 at 21:47

score 0 · Accepted Answer · answered Apr 20 '16 at 16:48

CREATE FUNCTION [dbo].[FuncOrder]
(
   @param1 int,
   @param2 int
)
RETURNS @returntable TABLE
(
   c1 int,
   c2 int
)
AS
BEGIN
   INSERT @returntable (c2, c1) --< That's the place to specify target columns
   SELECT @param1 as c2, @param2 as c1
   RETURN
END

T-SQL Function RETURNS @returntable COLUMN ALIAS ignored

1 Answers1