CODEWITHSUNDEEP
sqlsql-server
Cellman
Cellman

Reputation: 135

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

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

Upvotes: 8

Views: 11093

Answers (3)

PSK
PSK

Reputation: 17943

What you are looking for is not possible in SQL Server for Scalar valued Functions. Even the default SQL Server Scalar valued function return data with (No column name)

For example

 select Checksum('somedata')

Output

    (No column name)
 ---------------------
    -1791257452

I am not sure why you want the column name to come from function if you can use alias name, what benifit you are getting?

If you just want to know it is possible or not, in that case answer is "NO".

Only option you have is to use alias name with AS like follwong.

SELECT fullname(@fn, @mn,@ln) AS FullName

Upvotes: 2

Gaurang Dave
Gaurang Dave

Reputation: 4046

Do this:

SELECT fullname(@fn, @mn,@ln) AS COLUM_NAME_YOU_WANT

Create your function something like this: 

    create function concatall (
     @fn as TEXT, 
     @mn as TEXT,
     @ln as TEXT
    ) returns @merged table (
     [FullName] TEXT
    )
    as
    begin
     insert into @merged 
SELECT CONCAT(@fn , @mn , @ln)


     return;
    end

Upvotes: 2

Hasan Fathi
Hasan Fathi

Reputation: 6086

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

Upvotes: 7

Related Questions