use this scenario, it will help you to create your store procedure.
DECLARE @PRODUCT TABLE(
ID INT IDENTITY(1,1)
,PRODUCTCODE VARCHAR(MAX)
,PRODUCTNAME VARCHAR(MAX)
,PRODUCTPRIZE VARCHAR(MAX)
)
INSERT INTO @PRODUCT VALUES ('P001','PEN DRIVE','100')
INSERT INTO @PRODUCT VALUES ('P002','PEN DRIVE','110')
INSERT INTO @PRODUCT VALUES ('P003','PEN DRIVE','120')
INSERT INTO @PRODUCT VALUES ('P004','PEN','10')
INSERT INTO @PRODUCT VALUES ('P005','PEN','20')
INSERT INTO @PRODUCT VALUES ('P006','DRIVE','1000')
INSERT INTO @PRODUCT VALUES ('P007','LIGHT','200')
INSERT INTO @PRODUCT VALUES ('P008','MONITOR','10000')
DECLARE @SEARCHSTRING VARCHAR(MAX)
DECLARE @FIRESTWORD VARCHAR(MAX)
DECLARE @SECONDWORD VARCHAR(MAX)
SET @SEARCHSTRING = 'PEN DRIVE'
SELECT @FIRESTWORD = RTRIM(LTRIM(SUBSTRING(@SEARCHSTRING, 1,CHARINDEX(' ', @SEARCHSTRING)))),
@SECONDWORD = RTRIM(LTRIM(SUBSTRING(@SEARCHSTRING, CHARINDEX(' ', @SEARCHSTRING),LEN(@SEARCHSTRING))))
SELECT * FROM @PRODUCT WHERE PRODUCTNAME = @SEARCHSTRING OR PRODUCTNAME = @FIRESTWORD OR PRODUCTNAME = @SECONDWORD
Result will be like this...
ID PRODUCTCODE PRODUCTNAME PRODUCTPRIZE
1 P001 PEN DRIVE 100
2 P002 PEN DRIVE 110
3 P003 PEN DRIVE 120
4 P004 PEN 10
5 P005 PEN 20
6 P006 DRIVE 1000