USP_DATALIST_MERCHANDISEDEPARTMENT

            CREATE procedure dbo.USP_DATALIST_MERCHANDISEDEPARTMENT(@INCLUDEINACTIVE bit=0)
            as
            set nocount on;

            -- Departments

            select 
                ID,
                null DEPARTMENTID,
                NAME DEPARTMENTNAME,
                null CATEGORYNAME,
                DESCRIPTION,
                ISDISCOUNTABLE DEPARTMENTISDISCOUNTABLE,
                ISACTIVE,
                1 ISPARENT,
                0 ISCHILD
            from dbo.MERCHANDISEDEPARTMENT
            where @INCLUDEINACTIVE = 1
            or ISACTIVE = 1

            union all

            -- Categories

            select
                C.ID,
                D.ID DEPARTMENTID,
                null DEPARTMENTNAME,
                C.NAME CATEGORYNAME,
                C.DESCRIPTION,
                null DEPARTMENTISDISCOUNTABLE,
                C.ISACTIVE,
                0 ISPARENT,
                1 ISCHILD
            from dbo.MERCHANDISEDEPARTMENT D
            inner join dbo.MERCHANDISEDEPARTMENTCATEGORY C on D.ID = C.MERCHANDISEDEPARTMENTID
            where (@INCLUDEINACTIVE = 1 or (C.ISACTIVE = 1 and D.ISACTIVE = 1))

            order by ISPARENT,NAME, CATEGORYNAME