AMS - VB

CREATE FUNCTION dbo.CM_DEP_GETALL_CHILD_ID (@DEP_ID NVARCHAR(15))

RETURNS  @returnTable TABLE (DEP_ID VARCHAR(20))

AS

BEGIN

    DECLARE @TYPE_DEP VARCHAR(50) = (SELECT CD.TYPE FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @DEP_ID)

	IF(@TYPE_DEP = 'K')

		BEGIN

			INSERT INTO @returnTable (DEP_ID)

			SELECT CD.DEP_ID FROM CM_DEPARTMENT CD

			WHERE 1=1

			AND CD.KHOI_ID=@DEP_ID

            UNION 

            SELECT @DEP_ID

		END

	IF(@TYPE_DEP = 'TT')

		BEGIN

			INSERT INTO @returnTable (DEP_ID)

			SELECT CD.DEP_ID FROM CM_DEPARTMENT CD

			WHERE 1=1

			AND CD.FATHER_ID=@DEP_ID

            UNION 

            SELECT @DEP_ID

		END

	IF(@TYPE_DEP = 'PB')

		BEGIN

			INSERT INTO @returnTable (DEP_ID) SELECT @DEP_ID

		END

	RETURN;

END