• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 35
  • Last Modified:

Get total records before paging mechanisms takes effect

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
Stef Merlijn
Asked:
Stef Merlijn
  • 3
  • 2
2 Solutions
 
Barry CunneyCommented:
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
 
Stef MerlijnDeveloperAuthor 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.
0
 
Olaf DoschkeSoftware DeveloperCommented:
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Stef MerlijnDeveloperAuthor 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.
0
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
Stef MerlijnDeveloperAuthor Commented:
Thank you very much.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now