USP_SEARCHLIST_MERCHANDISEPRODUCT

            CREATE procedure dbo.USP_SEARCHLIST_MERCHANDISEPRODUCT
            (
                @NAME nvarchar(100) = null,
                @MERCHANDISEDEPARTMENTID uniqueidentifier = null,
                @MERCHANDISEDEPARTMENTCATEGORYID uniqueidentifier = null,
                @VENDORID uniqueidentifier = null,
                @INCLUDEINACTIVE bit = 0,
                @DISCOUNTABLEONLY bit = 0,
                @MAXROWS smallint = 500
            )
            as

                if @NAME is null
                    set @NAME = '';

                -- Wildcard character support

                set @NAME = replace(replace(@NAME,'[','\['),']','\]');
                set @NAME = replace(replace(@NAME,'*','%'),'?','_') + '%';

                select distinct top(@MAXROWS)
                    MP.ID,
                    P.NAME,
                    D.NAME DEPARTMENT,
                    C.NAME CATEGORY,
                    CONSTITUENT.NAME VENDOR
                from dbo.MERCHANDISEPRODUCT MP
                inner join dbo.PRODUCT P on P.ID = MP.ID
                inner join dbo.PRODUCTVENDOR PV on PV.PRODUCTID = P.ID
                inner join dbo.VENDOR V on V.ID = PV.VENDORID
                inner join dbo.CONSTITUENT on CONSTITUENT.ID = V.ID
                inner join dbo.MERCHANDISEDEPARTMENT D on MP.MERCHANDISEDEPARTMENTID = D.ID 
                left outer join dbo.MERCHANDISEDEPARTMENTCATEGORY C on MP.MERCHANDISEDEPARTMENTCATEGORYID = C.ID
                left outer join dbo.MERCHANDISEPRODUCTINSTANCE MPI on MP.ID = MPI.MERCHANDISEPRODUCTID
                left outer join MERCHANDISEPRODUCTINSTANCELOOKUPCODE on MERCHANDISEPRODUCTINSTANCELOOKUPCODE.MERCHANDISEPRODUCTINSTANCEID = MPI.ID
                where (
                    P.NAME like @NAME escape '\'
                    or MPI.BARCODE like @NAME escape '\'
                    or MPI.LOOKUPCODE like @NAME escape '\'
                    or MERCHANDISEPRODUCTINSTANCELOOKUPCODE.LOOKUPCODE like @NAME escape '\'
                )
                and (@MERCHANDISEDEPARTMENTID is null or D.ID = @MERCHANDISEDEPARTMENTID)
                and (@MERCHANDISEDEPARTMENTCATEGORYID is null or C.ID = @MERCHANDISEDEPARTMENTCATEGORYID)
                and (@VENDORID is null or PV.VENDORID = @VENDORID)
                and (@INCLUDEINACTIVE = 1 or MP.ISACTIVE = 1)
                and ((@DISCOUNTABLEONLY = 1 and D.ISDISCOUNTABLE = 1 and MP.ISDISCOUNTABLE = 1) or (@DISCOUNTABLEONLY = 0))
                order by P.NAME asc