Get the same result result of my query as a scalar function

Question

I created a user defined string_agg as a query and it's working fine I want to convert it to a scalar function but it is not returning the same result.

My query

SELECT
  STUFF(
    (SELECT '-' + CAST(Namess AS VARCHAR(20)) AS [text()]
     FROM TryDB
     FOR XML PATH('')), 1, 1, NULL) As String_Agg2;

It returns =>>> 1-2-3-4-5-6

Scalar function

function fun_JoinEmpColumnInfo8
(     
   @names varchar(20)
)  
returns nvarchar(20)  
as  
begin
return (
  SELECT
    STUFF(
      (SELECT ',' + CAST(@names AS VARCHAR(20)) AS [text()]
      FROM TryDB
      FOR XML PATH('')), 1, 1, NULL)
); 
end

It returns:

1,1,1,1,1
3,3,3,3,3
4,4,4,4,4
5,5,5,5,5
6,6,6,6,6

How can I get the same result as the query

`CAST(Namess AS VARCHAR(20))` <> `CAST(@names AS VARCHAR(20))` - one uses the parameter, one uses a column. If its still causing issues we need a [mre] on dbfiddle. — Dale K, Jul 17 '21 at 20:46
Your first query works against a *set of rows*; the scalar function uses a single atomic value; Look at *table-valued functions* — Stu, Jul 17 '21 at 20:51
Are you happy? Where is/are your ORDER BY clause(s)? You need that/those — SteveC, Jul 17 '21 at 22:12
the cast didn't work i just want the same result as the query — Mohamed Ramadan, Jul 18 '21 at 10:20
`(SELECT ',' + CAST(@names AS VARCHAR(20)) AS [text()] FROM TryDB FOR XML PATH('')), 1, 1, NULL)` doesn't use _any_ columns from `TryDB`. It takes the value of `@names` and, for each row in `TryDB`, adds another copy of `@names` to the string. Since you don't divulge an example of calling your function we don't know what it should be doing internally. Repeating "the same result as the query" doesn't make it any clearer. — HABO, Jul 19 '21 at 16:31

score 0 · Answer 1 · answered Jul 18 '21 at 16:11

table -->

create table TryDB (Namess int)
insert into TryDB values (1), (2),(3),(4),(5),(6)

create function without parameter.

create function fun_JoinEmpColumnInfo8()
 
returns nvarchar(20)  
as  
begin
return (
  SELECT
    STUFF(
      (SELECT '-' + CAST(namess AS VARCHAR(20)) AS [text()]
      FROM TryDB
      FOR XML PATH('')), 1, 1, NULL)
); 
end

Get the same result result of my query as a scalar function

1 Answers1