Well hi everyone, this is my first blog ever thus my first post ever. I work in IT since 2006 and have always been interested in databases since the beginning, but can also code various other languages. Enough about me...
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
