How to assign column name to return value of scalar function in sql serve

Question

I have defined a function in sql function, that inputs 3 names (three varchars) and outputs one name (one varchar)'. What I only want to is to give this return value a name/column name.

Right now when I call my function:

select concat_names(@first_name, @middle_name,@last_name)

I see returned varchar with no column name.

Here is complete code:

if object_id('[co1].[concat_names]') is not null
    drop function [co1].[concat_names];
go
create function [co1].[concat_names]
(
    @first_name varchar(20),
    @middle_name varchar(20),
    @last_name varchar(20)
)
returns varchar(62)
as
begin
    declare @full_name varchar(62)
    set @full_name = @first_name;
    if(@middle_name is not null)
    set @full_name = @full_name + ' ' + @middle_name;
    if(@last_name is not null)
    set @full_name = @full_name + ' ' + @last_name;
    return @full_name;
end
go

When I do:

select concat_names('A', 'B', 'C')

I get no column name in result set. I know I can rename the column name after its returned as no column name in above select query.

What I only want to know, if there is any way I can name the column while returning the value from the

Answer 1

Try this:

select fullname(@fn, @mn,@ln) AS 'yourName'

You can create table value function to return column with name, like this exmple:

create function udf_ParseDate (
 @date as datetime
) returns @dateinfo table (
 id int identity(1,1),
 [date] datetime,
 [year] int,
 [month] smallint,
 [day] smallint
)
as
begin
 insert into @dateinfo
 select @date, YEAR(@date), MONTH(@date), DAY(@date)

 return;
end

In your code:

if object_id('[co1].[concat_names]') is not null
    drop function [co1].[concat_names];
go
create function [co1].[concat_names]
(
    @first_name varchar(20),
    @middle_name varchar(20),
    @last_name varchar(20)
)
returns returns @info table (
   fullName varchar(256)
)
as
begin
    declare @full_name varchar(62)
    set @full_name = @first_name;
    if(@middle_name is not null)
    set @full_name = @full_name + ' ' + @middle_name;
    if(@last_name is not null)
    set @full_name = @full_name + ' ' + @last_name;
    insert into @info select @full_name;
    return;
end
go