Get Your Own Blog
If you would like to blog on SQLServerCentral.com then send an email to
Contact the author
for this blog
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!
- Posted on 7 December 2012
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 @SearchString VARCHAR(100) SET @SearchString = '%'+ @SearchString +'%' 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. -- 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) -- 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 WHERE p.Name LIKE@SearchString WHERE rslt.RsltNum BETWEEN @MinRsltNum AND@MaxRsltNum -- This filters the wanted row set.
Leave a comment on the original post
[mayoodbfun.blogspot.com, opens in a new window]