See the example below where I modified Gail Shaw's stored proc to add a start row and end row number for the results to be returned. Print out the @sSQL string before the sp_execute to see if it parses correctly and is exactly what you want.

```
CREATE PROCEDURE SearchHistory_Dynamic
(
@Product INT = NULL ,
@OrderID INT = NULL ,
@TransactionType CHAR(1) = NULL ,
@Qty INT = NULL,
@startRow INT,
@endRow INT
)
AS
DECLARE @sSQL NVARCHAR(2000) ,
@Where NVARCHAR(1000) = ''
SET @sSQL = ';WITH CTE AS (';
SET @sSQL = @sSQL + 'SELECT ProductID, ReferenceOrderID, TransactionType, Quantity, TransactionDate, ActualCost,
ROW_NUMBER() OVER( ORDER BY TransactionDate DESC) as RN
from Production.TransactionHistory '
IF @Product IS NOT NULL
SET @Where = @Where + 'AND ProductID = @_Product '
IF @OrderID IS NOT NULL
SET @Where = @Where + 'AND ReferenceOrderID = @_OrderID '
IF @TransactionType IS NOT NULL
SET @Where = @Where + 'AND TransactionType = @_TransactionType '
IF @Qty IS NOT NULL
SET @Where = @Where + 'AND Quantity = @_Qty '
IF LEN(@Where) > 0
SET @sSQL = @sSQL + 'WHERE ' + RIGHT(@Where, LEN(@Where) - 3)
SET @sSQL = @sSQL + ') SELECT * FROM CTE WHERE RN >= @_startRow AND RN <= @_endRow '
EXEC sp_executesql @sSQL,
N'@_Product int, @_OrderID int, @_TransactionType char(1), @_Qty int, @_startRow INT, @_endRow INT',
@_Product = @Product, @_OrderID = @OrderID,
@_TransactionType = @TransactionType, @_Qty = @Qty,
@_startRow = @startRow, @_endRow = @endRow;
GO
```