Return the name and deaprtment in single column using coalesce and function

-- Create some temp tables
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DEPT]') AND type in (N'U'))
DROP TABLE [dbo].[DEPT]
GO
CREATE TABLE DEPT (ID INT , DEPT VARCHAR(100))
INSERT INTO DEPT VALUES (10,'ACCOUNTING')
INSERT INTO DEPT VALUES (20,'MARKETING')
INSERT INTO DEPT VALUES (30,'FINANCE')
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SNAME]') AND type in (N'U'))
DROP TABLE [dbo].[SNAME]
GO
CREATE TABLE SNAME (ID INT , DEPT INT , SNAME VARCHAR(100))
INSERT INTO SNAME VALUES (1,10,'HARISH')
INSERT INTO SNAME VALUES (2,20,'HARISH')
INSERT INTO SNAME VALUES (2,20,'CIJU')
INSERT INTO SNAME VALUES (2,30,'CIJU')
INSERT INTO SNAME VALUES (5,10,'CIJU')
GO
-- Drop and create the Functions for Comma seperated value return.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[COMA_CP1]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[COMA_CP1]
GO
CREATE FUNCTION COMA_CP1(@SNAME1 VARCHAR(100)) RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @RES VARCHAR(100)
SELECT @RES = COALESCE(@RES + ', ', '') + DEPT1.DEPT
FROM SNAME
INNER JOIN DEPT DEPT1
ON SNAME.DEPT = DEPT1.ID
WHERE SNAME = @SNAME1
RETURN @SNAME1 +','+ @RES
END
GO
-- Final select to Return
SELECT DBO.COMA_CP1(SNAME)
FROM SNAME
INNER JOIN DEPT DEPT1
ON SNAME.DEPT = DEPT1.ID
GROUP BY SNAME