COALESCE Function with Realtime Example

Coalesce function will be helpful if we use it with tricky way.

Basically this function accepts n number of arguments and returns the first non null value, which is passed as parameter.

There are many situations we need to bring the multiple row values in a single string ( concatenated string). The following is the Example with necessary Screenshot and SQL Function to accomplish this.

The SQL for GetStudents function is as follows

CREATE function [dbo].GetStudents
(
    @className varchar(max)
)
RETURNS VARCHAR(MAX)
AS
BEGIN--Main

DECLARE @x varchar(8000)

SELECT @x = COALESCE(@x+', ','')+ISNULL([StudentName],'')
FROM [BidSmartTemp1].[dbo].[ClassTable]
WHERE ClassName = @className
RETURN @x

END