Retrieve Comma Separated Values

Some times there are certain scenarios where data needs to be gathered as a collection. For instance multiple records need to be set as single record, in a such cases setting row data as comma separated column value can be quite effective.

SQL server allows to incorporate this with its built-in feature of stuff allowing row data to be merged as a single column value.

Code:

select  

(STUFF((SELECT ',' + CAST(a.username AS VARCHAR(10))+''' ' [text()]

FROM [users] a

FOR XML PATH(''), TYPE)

.value('.','NVARCHAR(MAX)'),1,2,' '))

as AllNames 

Output:

User-Defined Functions in SQL Server

In SQL Server user can define function to return a table or some value. Following are two ways of creating function:

1- Returning Table

-----------------Returing table

create function printTwo

(@projectId varchar, @resourceId varchar)

returns table as

return

(

select @projectId as [proj],@resourceId as [resc]

);

2- Returning Scalar(single) value

-----------------Returing scalar

create function printOne

(

@value int

)

returns int

WITH EXECUTE AS CALLER 

as

begin

return @value

end 

3- Checking the output by calling in different ways 

select dbo.printOne(1) 'Result';

select * from dbo.printTwo('1','2');

select dbo.printOne(1) 'Result', * from dbo.printTwo('a','b');