CREATE PROCEDURE dbo.SEARCHSALEITEM
(
    @BRAND VARCHAR(100),
    @ITEMID VARCHAR(100),
    @ITEMNAME VARCHAR(100)
)
AS
SELECT *
FROM MSTRITEM AS M JOIN ITEMIMAG AS I
ON I.ITEMID = M.ITEMID
WHERE (M.ADDTOSALEITEM=1) AND 
((M.BRAND LIKE '%' + @BRAND + '%') OR (M.ITEMNAME LIKE '%' + @ITEMNAME + '%'))
AND ',' + @ITEMID + ',' NOT LIKE '%,' + CONVERT(VARCHAR(20), M.ITEMID) + ',%'

GO

CREATE PROCEDURE dbo.SEARCHSALEITEM_TEST
(
    @BRAND VARCHAR(100),
    @ITEMID VARCHAR(100),
    @ITEMNAME VARCHAR(100),
    @LIKE VARCHAR(10)

)
AS
If @LIKE='LIKE'
	SELECT *
	FROM MSTRITEM AS M JOIN ITEMIMAG AS I
	ON I.ITEMID = M.ITEMID
	WHERE (M.ADDTOSALEITEM=1) AND 
	((M.BRAND LIKE '%' + @BRAND + '%') OR (M.ITEMNAME LIKE '%' + @ITEMNAME + '%'))
	AND ',' + @ITEMID + ',' LIKE '%,' + CONVERT(VARCHAR(20), M.ITEMID) + ',%'

else If @LIKE='NOT LIKE'
	SELECT *
	FROM MSTRITEM AS M JOIN ITEMIMAG AS I
	ON I.ITEMID = M.ITEMID
	WHERE (M.ADDTOSALEITEM=1) AND 
	((M.BRAND LIKE '%' + @BRAND + '%') OR (M.ITEMNAME LIKE '%' + @ITEMNAME + '%'))
	AND ',' + @ITEMID + ',' NOT LIKE '%,' + CONVERT(VARCHAR(20), M.ITEMID) + ',%'
GO