Solved

Get total records before paging mechanisms takes effect

Posted on 2016-08-02
6
27 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

705 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

19 Experts available now in Live!

Get 1:1 Help Now