USP_SEARCHLIST_MERCHANDISEPRODUCTINSTANCE

            CREATE procedure dbo.USP_SEARCHLIST_MERCHANDISEPRODUCTINSTANCE
            (
                @BARCODE nvarchar(25) = null,
                @NAME nvarchar(50) = null,
                @MERCHANDISEDEPARTMENTID uniqueidentifier = null,
                @MERCHANDISEDEPARTMENTCATEGORYID uniqueidentifier = null,
                @VENDORID uniqueidentifier = null,
                @MAXROWS smallint = 500
            )
            as
                if @NAME is null
                    set @NAME = '';

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

                -- Wildcard character support

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

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

                select top(@MAXROWS)
                    MPI.ID,
                    MPI.BARCODE,
                    MPI.ITEMDETAILS,
                    MPI.ONHANDQUANTITY,
                    MPI.SALEPRICE
                from dbo.MERCHANDISEPRODUCT MP
                inner join dbo.PRODUCT P on MP.ID = P.ID
                inner join dbo.PRODUCTVENDOR PV on PV.PRODUCTID = P.ID
                inner join dbo.MERCHANDISEPRODUCTINSTANCE MPI on MP.ID = MPI.MERCHANDISEPRODUCTID
                left outer join dbo.MERCHANDISEDEPARTMENTCATEGORY MDC on MP.MERCHANDISEDEPARTMENTCATEGORYID = MDC.ID
                where P.NAME like @NAME
                and (@MERCHANDISEDEPARTMENTID is null or MP.MERCHANDISEDEPARTMENTID = @MERCHANDISEDEPARTMENTID)
                and (@MERCHANDISEDEPARTMENTCATEGORYID is null or MP.MERCHANDISEDEPARTMENTCATEGORYID = @MERCHANDISEDEPARTMENTCATEGORYID)
                and (MPI.BARCODE like @BARCODE or MPI.LOOKUPCODE like @BARCODE or exists (select ID from MERCHANDISEPRODUCTINSTANCELOOKUPCODE where MERCHANDISEPRODUCTINSTANCELOOKUPCODE.MERCHANDISEPRODUCTINSTANCEID = MPI.ID and MERCHANDISEPRODUCTINSTANCELOOKUPCODE.LOOKUPCODE like @BARCODE))
                and (@VENDORID is null or PV.VENDORID = @VENDORID)
                and MPI.ISACTIVE = 1
                and MP.ISACTIVE = 1
                order by P.NAME
                option (recompile);