SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Returning row count per page

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.


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

      Id    INT IDENTITY(1,1),
      Name  VARCHAR(100)

WHILE@ProductNumber <= @MaxProducts
      INSERT INTO #Products VALUES ('Product ' + CONVERT(VARCHAR,@ProductNumber))
      SET @ProductNumber =@ProductNumber + 1

-- Start by reading the subquery. It will be where you do all the work.
SELECT      rslt.Name,
            -- 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

Having Fun with Database

Jonathan is an all-rounder developer specialized in database, both TSQL and Oracle. He has 6 years of experience and is always learning new tricks. Learning the language is one thing but Jonathan likes to put togheter solutions that utilizes many features. The result is some easy to use code for every kind of situation. He also likes to standardize as much as possible, each solution often has many real word usages. Now let the code talk!


Leave a comment on the original post [mayoodbfun.blogspot.com, opens in a new window]

Loading comments...