I fixed the stored procedure that does the search from the manage products screen. Run the following stored proc in your database if your database uses an objectqualifier make sure you add yours. Changes highlighted in yellow:
/****** Object: StoredProcedure [dbo].[Smith_productByFilter] Script Date: 08/27/2010 00:11:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Smith_productByFilter]
@Page int,
@RecsPerPage int,
@sortfield varchar(50),
@sortorder varchar(10),
@filter varchar(50),
@searchtext varchar(50) ,
@tabModuleId int,
@PortalId int
as
CREATE TABLE #TempItems
(
ProductAutoID int IDENTITY,
ProductID int
)
DECLARE @SearchSQL varchar(5000)
if(@Filter='All')
Begin
SELECT @SearchSQL ='INSERT INTO #TempItems (ProductID) select P.ProductID from Smith_Products P left outer join ' +
'Smith_Category C on C.CategoryID=P.CategoryID'+
' where ( p.TabModuleId = ' + CAST ( @tabModuleId AS VARCHAR(10)) + ' OR p.Share = 1 ) and P.LogicallyDeleted !=''1'''
if( @sortfield='CategoryName')
SELECT @SearchSQL=@SearchSQL+' '+' order by C.' +@sortfield+' '+ @sortorder
else
SELECT @SearchSQL=@SearchSQL+' '+' order by P.'+@sortfield+' '+ @sortorder
End
else if(@Filter='CategoryName')
Begin
SELECT @SearchSQL ='INSERT INTO #TempItems (ProductID) select P.ProductID from Smith_Products P inner join ' +
' Smith_CategoryProduct CP on CP.ProductID=P.ProductID inner join Smith_Category C on C.CategoryID=CP.CategoryID'+
' where ( p.TabModuleId = ' + CAST ( @tabModuleId AS VARCHAR(10)) + ' OR p.Share = 1 ) and P.LogicallyDeleted !=''1'' and ( C.'+@filter+' Like ''%' + @searchtext + '%'')'
if( @sortfield='CategoryName')
SELECT @SearchSQL=@SearchSQL+' '+' order by C.' +@sortfield+' '+ @sortorder
else
SELECT @SearchSQL=@SearchSQL+' '+' order by P.'+@sortfield+' '+ @sortorder
End
else
Begin
SELECT @SearchSQL ='INSERT INTO #TempItems (ProductID) select p.ProductID from Smith_Products P ' +
' where ( p.TabModuleId = ' + CAST ( @tabModuleId AS VARCHAR(10)) + ' OR p.Share = 1 ) and P.LogicallyDeleted !=''1'' and ( P.'+@filter+' Like ''%' + @searchtext + '%'')'
if( @sortfield='CategoryName')
SELECT @SearchSQL=@SearchSQL+' '+' order by C.' +@sortfield+' '+ @sortorder
else
SELECT @SearchSQL=@SearchSQL+' '+' order by P.'+@sortfield+' '+ @sortorder
End
EXECUTE(@SearchSQL)
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
SELECT p.*,c.categoryname, TotalRecords=(
SELECT COUNT(*)
FROM #TempItems TI),
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.ProductAutoID >= @LastRec
)
FROM Smith_Products P left outer join
Smith_Category C on C.CategoryID=P.CategoryID RIGHT OUTER JOIN
#TempItems T ON P.ProductID = T.ProductID
WHERE ProductAutoID > @FirstRec AND ProductAutoID < @LastRec and p.PortalID = @PortalId