Get total records before paging mechanisms takes effect

Stef Merlijn
Stef Merlijn used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Stef MerlijnDeveloper

Author

Commented:
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.
Software Developer
Commented:
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.
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Stef MerlijnDeveloper

Author

Commented:
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.
Olaf DoschkeSoftware Developer
Commented:
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.
Stef MerlijnDeveloper

Author

Commented:
Thank you very much.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial