Here is a little trick that can save you some loading time in your application. Various methods are used to have a combination of current rows on max rows shown (0-100 of 200000). By using this code, you can simplify the returns of the procedures and get all the information in one single database call. We will mainly use the OVER clause here to achieve our goal. I will update later to include the Oracle version.
TSQL
DECLARE @ProductNumber INT
DECLARE @MaxProducts INT
DECLARE @SearchString VARCHAR(100)
DECLARE @MinRsltNum INT
DECLARE @MaxRsltNum INT
SET @ProductNumber = 1
SET @MaxProducts = 50
SET @SearchString = '2'
SET @SearchString = '%'
+ @SearchString +
'%'
SET @MinRsltNum = 1
SET @MaxRsltNum = 5
CREATE TABLE #Products
(
Id INT IDENTITY(1,1),
Name VARCHAR(100)
)
WHILE
@ProductNumber <= @MaxProducts
BEGIN
INSERT INTO #Products
VALUES ('Product ' + CONVERT(VARCHAR,
@ProductNumber))
SET @ProductNumber =
@ProductNumber + 1
END
-- Start by reading the subquery. It will be where you
do all the work.
SELECT rslt.Name,
rslt.RsltNum,
rslt.RsltTotalCount,
-- And to play a little with data!
'Viewing ' + CONVERT(VARCHAR, MIN(rslt.RsltNum) OVER (PARTITION BY 1))
+ ' - ' + CONVERT(VARCHAR, MAX(rslt.RsltNum) OVER (PARTITION BY 1))
+ ' of ' + CONVERT(VARCHAR, rslt.RsltTotalCount)
FROM ( SELECT p.Name,
-- This gets the row number. The ORDER BY is important to
ensure a stable result set.
ROW_NUMBER() OVER (ORDER BY Name) AS RsltNum,
-- This will return the total number of rows found before
filtering the wanted row set.
COUNT(*) OVER (PARTITION BY 1) AS RsltTotalCount
FROM #Products
p
WHERE p.Name LIKE
@SearchString
) rslt
WHERE rslt.RsltNum BETWEEN @MinRsltNum AND
@MaxRsltNum -- This filters the wanted row set.
DROP TABLE #Products
