I'm aware that the PagedDataSource and Datagrid controls still receive all records matching a query before paging them. Obviously when there is a large result set this could impact seriously on performance.

I am currently developing a website that uses a PagedDataSource with a repeater that uses the following stored procedure to generate results:
Code:
CREATE  PROCEDURE SY_SearchProducts
(
	@BrandID	int,
	@CategoryID	int,
	@StyleID	int,
	@SizeID		int,
	@ColourID	int
)
AS

	DECLARE	@SQL		varchar(5000)

	SET @SQL = 		'SELECT		PD.ProductID, PD.ProductName, '
	SET @SQL = @SQL + 	'		PD.ProductDescription, PD.RRP, PD.Price, '
	SET @SQL = @SQL + 	'		PD.CreationDate, PD.LastModified, PD.Visible, '
	SET @SQL = @SQL + 	'		BD.BrandID, BD.BrandName, '
	SET @SQL = @SQL + 	'		BD.BrandAbbreviation, BD.Visible AS BrandVisible '
	SET @SQL = @SQL + 	'FROM		Product_Directory AS PD '
	SET @SQL = @SQL + 	'INNER JOIN	Brand_Directory AS BD '
	SET @SQL = @SQL + 	'ON 		PD.BrandID = BD.BrandID '
	SET @SQL = @SQL + 	'INNER JOIN	Product_Items AS PrI '
	SET @SQL = @SQL + 	'ON		PD.ProductID = PrI.ProductID '
	SET @SQL = @SQL + 	'INNER JOIN	Product_Stock AS PS '
	SET @SQL = @SQL + 	'ON		PrI.ItemID = PS.ItemID '
	SET @SQL = @SQL + 	'RIGHT JOIN	Product_Category AS PC '
	SET @SQL = @SQL + 	'ON		PD.ProductID = PC.ProductID '
	SET @SQL = @SQL + 	'INNER JOIN	Category_Directory AS CD '
	SET @SQL = @SQL + 	'ON		PC.CategoryID = CD.CategoryID '
	SET @SQL = @SQL + 	'RIGHT JOIN	Product_Style AS PSt '
	SET @SQL = @SQL + 	'ON		PD.ProductID = PSt.ProductID '
	SET @SQL = @SQL + 	'INNER JOIN	Style_Directory AS SD '
	SET @SQL = @SQL + 	'ON		PSt.StyleID = SD.StyleID '
	SET @SQL = @SQL + 	'WHERE		BD.Visible = 1 '
	SET @SQL = @SQL + 	'AND		PD.Visible = 1 '
	SET @SQL = @SQL + 	'AND		PrI.Visible = 1 '
	SET @SQL = @SQL + 	'AND		PS.StockAvailable > 0 '
	SET @SQL = @SQL + 	'AND		SD.Visible = 1 '
	SET @SQL = @SQL + 	'AND		CD.Visible = 1 '
	
	IF @BrandID != 0
		SET @SQL = @SQL +	'AND		BD.BrandID = ' + CONVERT(varchar(10), @BrandID)

	IF @CategoryID != 0
		SET @SQL = @SQL +	'AND		CD.CategoryID = ' + CONVERT(varchar(10), @CategoryID)

	IF @StyleID != 0
		SET @SQL = @SQL +	'AND		SD.StyleID = ' + CONVERT(varchar(10), @StyleID)

	IF @SizeID != 0
		SET @SQL = @SQL +	'AND		PS.SizeID = ' + CONVERT(varchar(10), @SizeID)

	IF @ColourID != 0
		SET @SQL = @SQL +	'AND		PrI.ColourID = ' + CONVERT(varchar(10), @ColourID)

	SET @SQL = @SQL + 	'GROUP BY	PD.ProductID, PD.ProductName, '
	SET @SQL = @SQL + 	'		PD.ProductDescription, PD.RRP, PD.Price, '
	SET @SQL = @SQL + 	'		PD.CreationDate, PD.LastModified, PD.Visible, '
	SET @SQL = @SQL + 	'		BD.BrandID, BD.BrandName, '
	SET @SQL = @SQL + 	'		BD.BrandAbbreviation, BD.Visible '
	
	SET @SQL = @SQL + 	'ORDER BY	PD.Price ASC, BD.BrandName ASC, PD.ProductName ASC '
	EXEC(@SQL)
GO
I am estimating that this stored procedure could return up about 5000 records. Do I need to look at implementing paging within the stored procedure rather than in ASP.NET?

As the stored procedure uses dynamic SQL I'm not quite sure how to achieve this.

Any help on this or any other optimisation tips would be much appreciated.

DJ