Solved

Get total records before paging mechanisms takes effect

Posted on 2016-08-02
6
31 Views
Last Modified: 2016-08-04
Hi,

For Stored Procedure at the bottom I want to get the total number of records that would be retrieved before paging mechanism takes effect. This is than used to calculate the total number of pages for the selection.
-- Something like???:
  SELECT Count(*) AS TotalRecords  FROM  ZoekfunctieBron

Open in new window


CREATE PROCEDURE [dbo].[sp_Zoekfunctie] ( 
           @ZoekTekst                  nVarchar(255) 
         , @RecordsOnPage              int 
         , @SkipPages                  int 
      ) 
AS      
BEGIN   
  ;     
  WITH ZoekfunctieBron ( ID, GevondenTekst, Herkenningstekst, Veldnaam, Tabel, VeldType, IsVerwijderd, RowNumber ) 
  AS ( SELECT Z1.ID, Z1.GevondenTekst, Z1.Herkenningstekst, Z1.Veldnaam, Z1.Tabel, Z1.VeldType, Z1.IsVerwijderd,   
       ROW_NUMBER() OVER ( ORDER BY Z1.ID, Z1.Tabel ) AS RowNumber 
       FROM dbo.vwZoekFunctie AS z1                                
       WHERE Z1.GevondenTekst like @Zoektekst                      
     ) 
     SELECT TOP ( @RecordsOnPage )  
       Z2.ID, Z2.GevondenTekst, Z2.Herkenningstekst, Z2.Veldnaam, Z2.Tabel, Z2.VeldType, Z2.IsVerwijderd, Z2.RowNumber 
    FROM  ZoekfunctieBron AS Z2                                    
    WHERE Z2.RowNumber BETWEEN ((@SkipPages * @RecordsOnPage) + 1) 
                           AND ((@SkipPages + 1) * @RecordsOnPage) 
  ;  
END

Open in new window

0
Comment
Question by:Delphiwizard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 41739347
Possibly something like the following may help you - i.e. store count in variable for later use
DECLARE @Row_Count BIGINT
SELECT @Row_Count = Count(*)  FROM  ZoekfunctieBron

Open in new window

0
 

Author Comment

by:Delphiwizard
ID: 41739436
But how do I get the variable back with the result?
Also where do I put this code? When I put it ib between the two SELECT statements It is not allowed.
0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 500 total points
ID: 41740269
You can't use a CTE in two subsequent selects, so you can't Select Count(*) FROM  ZoekfunctieBron and then do the page query.

What you'd need is a separate query with

SELECT @Totalcount = Count(*) 
       FROM dbo.vwZoekFunctie                                
       WHERE GevondenTekst like @Zoektekst 

Open in new window


To return this within the same stored procedure @Totalcount will need to be a parameter with OUTPUT clause. Or you make that an extra user defined function. That's the better idea, as you only need to retrieve another rowcount, if @Zoektekst changes or the amount of data like @Zoektekst changes, you will not like to redo this for every page you retrieve, especially as a LIKE clause can't be optimized.

In that aspect, you better introduce full-text indexing on the GevondenTekst field and use WHERE CONTAINS(GevondenTekst , @Zoektekst)

Bye, Olaf.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Delphiwizard
ID: 41740572
Is full-text indexing available and activate by default in Express-editions?
Does it require special settings on the field level?
Can full-text indexing be used on a view? As I search in multiple tables in a view from inside the SP.
0
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 500 total points
ID: 41741235
Available: yes. Active by default: No, neither in Express or any other edition.
You first create a full-text catalog, then define full-text indexes. Views query tables, and so the full-text indexed columns can be used in the CONTAINS function and other full-text related functions, both when querying the table directly or via a view.

Bye, Olaf.
0
 

Author Closing Comment

by:Delphiwizard
ID: 41742219
Thank you very much.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question