Solved

Get total records before paging mechanisms takes effect

Posted on 2016-08-02
6
28 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
  • 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
Viewers will learn how the fundamental information of how to create a table.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now