How to write storedprocedure for search?

I have one tabe Name Product

ApplicationId uniqueidentifier
ProductId int
ProductTypeId int
ProductCode nvarchar(100)
ProductName nvarchar(max)
ProductFor nvarchar(max)
summary nvarchar(max)
Price money
TaggedPrice money
Keyword nvarchar(max)
Active Bit

My requirement is i want to write single procedure for search based on productCode,ProductName,ProductFor.
I have written for one search based on Productname
create procedure search
@ApplicationId Uniqueidentifier,
@Search nvarchar(max)
@Active Bit
As
Begin
select @searchLike=CASE WHEN @Search <> '' THEN 'AND product.productName LIKE ''' +@Search+'%' '' ELSE '' END

EXEC ('SELECT ProductId,ProductName from Products where ApplicationId=''' @Application+''' AND Active=''' +@Active+''''+
@searchLike+' order by productid')
ENd

but i want to write for three Fields single proc.(Productname,ProductFor,ProductCode)

plz help me

Thanks inadvance

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Mittal Sejpal15y ago
Try like This

Create Procedure search

@ProductCode nvarchar(100),
    @ProductFor nvarchar(max) ,
@ProductName nvarchar(max)

As

Declare @Str nvarchar(MAX)

SET @Str = 'Select ColumName1,... from Product where 1=1 '

if len(@ProductCode)>0
Begin
SET @Str=@Str + ' And ProductCode = ' + @ProductCode
End

  if len(@ProductFor)>0
Begin
SET @Str=@Str + ' And ProductFor= ' + @ProductFor
End

  if len(@ProductName)>0
Begin
SET @Str=@Str + ' And ProductName= ' + @ProductName
End

Print(@Str)
Exec(@Str)

this will list all records by default and will list record according to the search criteria provided

0