How to create scarlar value function that input multivalue function

sql-server-2005

How to create t-sql scalar value function that input string multivalued and that sould
return string1 + string2 + string3.

Best Solution

If you are not looking for this please let me know (Just an example)

First create a table with some values

like

create table tbl (id int, col1 varchar(50),col2 varchar(50), col3 varchar(50))
insert into tbl values(1,'colval1','colval2','colval3')


CREATE FUNCTION fn_TestFunc
(   
    @param1 int
)
RETURNS  varchar(max)
AS
BEGIN   
    declare @result varchar(max)

    select @result =  Col1+ ',' + Col2 + ',' + Col3
    from tbl t
    where t.ID = @param1

    return @result


END

To execute the function select dbo.fn_TestFunc(1)

Output:

Col1Val , Col2Val , Col3Val

1 more example

ALTER FUNCTION [dbo].[fn_TestFunc]
(
    -- Add the parameters for the function here
    @param1 int
)
RETURNS  nvarchar(max)
AS
BEGIN

    declare @result varchar(max)
    SELECT @result= 
    (
        SELECT 
            CAST(Col1 AS varchar(max)) + ',' + CAST(Col1 AS varchar(max)) + '|'
        from tbl t
                where t.ID = @param1
        FOR XML PATH ('')
    ) 
    return LEFT(@result,LEN(@result) - 1)
END

Output

Col1Val , Col2Val
Related Question