ALTER FUNCTION dbo.FN_GET_BRANCH_LEADER
( @BRANCH_ID VARCHAR(15), @DEPT_ID VARCHAR(50) )

RETURNS VARCHAR(MAX)

AS

BEGIN
DECLARE @BRANCH_TYPE VARCHAR(15), @TLNAME VARCHAR(100)
DECLARE @LST_USER_RECIVE TABLE (TLNAME VARCHAR(10))
SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @BRANCH_ID)
IF(@BRANCH_TYPE = 'PGD' )
BEGIN
	SET @TLNAME = (SELECT STUFF((SELECT '; ' + TLFullName FROM TL_USER 
		WHERE 1=1
		AND TLSUBBRID = @BRANCH_ID 
		AND RoleName IN ('TPGD','PPGD','GDDV')
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'),1,2,''))
END
ELSE IF(@BRANCH_TYPE = 'CN' )
BEGIN
	SET @TLNAME = (SELECT STUFF((SELECT '; ' + TLFullName FROM TL_USER 
		WHERE 1=1
		AND TLSUBBRID = @BRANCH_ID 
		AND RoleName IN ('GDDV','PGD')
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'),1,2,''))
END
ELSE IF(@BRANCH_TYPE = 'HS' )
BEGIN
	SET @TLNAME = (SELECT STUFF((SELECT '; ' + TLFullName FROM TL_USER 
		WHERE 1=1
		AND TLSUBBRID = @BRANCH_ID
		AND SECUR_CODE = @DEPT_ID
		AND RoleName IN ('GDDV','PP','TP')
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'),1,2,''))
END
ELSE
BEGIN
  SET @TLNAME = (SELECT STUFF((SELECT '; ' + TLFullName FROM TL_USER 
		WHERE 1=1
		AND TLSUBBRID = @BRANCH_ID
		AND SECUR_CODE = @DEPT_ID
		AND RoleName IN ('GDDV')
		FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'),1,2,''))
END
RETURN @TLNAME
END;
GO