USP_SEARCHLIST_MERCHANDISEVENDOR

            CREATE procedure dbo.USP_SEARCHLIST_MERCHANDISEVENDOR
            (
                @NAME nvarchar(100) = null,
                @LOOKUPID nvarchar(100) = null,
                @DEPARTMENTID uniqueidentifier = null,
                @BARCODE nvarchar(25) = null,
                @COUNTRYID uniqueidentifier = null,
                @ADDRESSBLOCK nvarchar(150) = null,
                @CITY nvarchar(50) = null,
                @STATEID uniqueidentifier = null,
                @POSTCODE nvarchar(12) = null,
                @INCLUDEINACTIVE bit = 0,
                @MAXROWS smallint = 500
            )
            as

                if @NAME is not null
                begin                    
                    set @NAME = replace(replace(@NAME,'[','\['),']','\]');
                    set @NAME = replace(replace(@NAME,'*','%'),'?','_') + '%';
                end

                if @BARCODE is not null
                begin
                    set @BARCODE = replace(replace(@BARCODE,'[','\['),']','\]');
                    set @BARCODE = replace(replace(@BARCODE,'*','%'),'?','_') + '%';
                end

                if @LOOKUPID is not null
                begin
                    set @LOOKUPID = replace(replace(@LOOKUPID,'[','\['),']','\]');
                    set @LOOKUPID = replace(replace(@LOOKUPID,'*','%'),'?','_') + '%';
                end

                if @ADDRESSBLOCK is not null
                begin
                    set @ADDRESSBLOCK = replace(replace(@ADDRESSBLOCK,'[','\['),']','\]');
                    set @ADDRESSBLOCK = replace(replace(@ADDRESSBLOCK,'*','%'),'?','_') + '%';
                end

                if @CITY is not null
                begin
                    set @CITY = replace(replace(@CITY,'[','\['),']','\]');
                    set @CITY = replace(replace(@CITY,'*','%'),'?','_') + '%';
                end

                if @POSTCODE is not null
                begin
                    set @POSTCODE = replace(replace(@POSTCODE,'[','\['),']','\]');
                    set @POSTCODE = replace(replace(@POSTCODE,'*','%'),'?','_') + '%';
                end

                declare @CHECKADDRESS bit = 0
                if @ADDRESSBLOCK is not null or @COUNTRYID is not null or @STATEID is not null or @POSTCODE is not null or @CITY is not null
                    set @CHECKADDRESS = 1

                select distinct top(@MAXROWS)
                    V.ID,
                    C.NAME,
                    C.LOOKUPID,
                    A.DESCRIPTION ADDRESS,
                    COUNTRY.[DESCRIPTION] COUNTRY
                from 
                    dbo.VENDOR V
                inner join
                    dbo.CONSTITUENT C on C.ID = V.ID
                left outer join
                    dbo.PRODUCTVENDOR PV on PV.VENDORID = V.ID
                left outer join
                    dbo.ADDRESS A on A.CONSTITUENTID = C.ID and A.ISPRIMARY = 1
                left outer join
                    dbo.COUNTRY on A.COUNTRYID = COUNTRY.ID
                left outer join
                    dbo.PHONE P on P.CONSTITUENTID = C.ID and P.ISPRIMARY = 1
                left outer join
                    dbo.EMAILADDRESS E on E.CONSTITUENTID = C.ID and E.ISPRIMARY = 1
                left outer join
                    dbo.MERCHANDISEPRODUCT MP on MP.ID = PV.PRODUCTID
                left outer join
                    dbo.MERCHANDISEPRODUCTINSTANCE MPI on MP.ID = MPI.MERCHANDISEPRODUCTID
                where (@NAME is null or C.NAME like @NAME escape '\')
                and (@LOOKUPID is null or C.LOOKUPID like @LOOKUPID escape '\')
                and (@DEPARTMENTID is null or MP.MERCHANDISEDEPARTMENTID = @DEPARTMENTID)
                and (@BARCODE is null or MPI.BARCODE like @BARCODE escape '\' or MPI.LOOKUPCODE like @BARCODE escape '\')
                and (@ADDRESSBLOCK is null or A.ADDRESSBLOCK like @ADDRESSBLOCK escape '\')
                and (@COUNTRYID is null or A.COUNTRYID = @COUNTRYID)
                and (@CITY is null or A.CITY like @CITY escape '\')
                and (@STATEID is null or A.STATEID = @STATEID)
                and (@POSTCODE is null or A.POSTCODE like @POSTCODE escape '\')
                and (@INCLUDEINACTIVE = 1 or C.ISINACTIVE = 0)
                order by 
                    C.NAME asc