May This Help You
CREATE PROC[dbo].[Product_Search]
(@KeyWord AS VARCHAR(MAX))
AS
DECLARE @qSel AS VARCHAR(MAX)
DECLARE @qWhere AS VARCHAR(MAX)
DECLARE @query AS VARCHAR(MAX)
SET @qSel = 'SELECT * FROM [Products] cc '
SET @qWhere = ' '
IF LEN(@KeyWord) > 1
BEGIN
SET @qWhere = @qWhere + 'cc.ProductName Like ''%' + @KeyWord + '%'' '
END
ELSE
BEGIN
SET @qWhere = @qWhere + 'cc.ProductName Like ''' + @KeyWord + '%'' '
END
IF LEN(@qWhere) != 0
SET @qWhere = 'WHERE ' + @qWhere
SET @query = @qSel + @qWhere
PRINT (@query)
EXEC (@query)
if you enter 'j' then the result will base on this query
SELECT * FROM [Products] cc WHERE cc.ProductName Like 'j%'
if you enter 'jeans' then the result will base on this query
SELECT * FROM [Products] cc WHERE cc.ProductName Like '%jeans%'
I Check Length of Key Word in
IF LEN(@KeyWord) > 1
you can change it if you want it depends on your requirement . can also add order by clause if you want by changing the following line.
SET @query = @qSel + @qWhere + ' order by cc.ProductName ASC '
or any other Column