Paging stored procedure using row_number ()

Paging is used to create a brand new project today. We can find a paging stored procedure from the previous project. We can directly pass in the table name for paging and use row_number () function, so it is only applicable to mssql2005 and above. First, write it down for future use.

Code   --   ========================================================== =====
-- Author: Ox's nest
-- Create Date: 2009 - 07 - 22   12 : 41
-- Description: pagination. row_number () is used ()
--   ========================================================== =====
Create procedure [DBO]. [proc_showpage]
@ Tblname varchar ( 255 ), -- Table Name
@ Strgetfields varchar ( 1000 ) =   ' * ' , -- Columns to be returned. Default Value: *
@ Strorder varchar ( 255 ) = '' , -- Name of the sorted field, required
@ Strordertype varchar ( 10 ) = ' ASC ' , -- Sorting method. The default value is ASC.
@ Pagesize Int   =   10 , -- Page size. The default value is 10.
@ Pageindex Int   =   1 , -- Page number. The default value is 1.
@ Strwhere varchar ( 1500 ) =   ''   -- Query conditions (Note: Do not add Where )
As

Declare @ strsql varchar (5000)

If@ Strwhere! =''
Set@ Strwhere='Where'+@ Strwhere

Set @ Strsql =
' Select * from ( ' +
' Select row_number () over (order ' + @ Strorder + '   ' + @ Strordertype + ' ) As pos, ' + @ Strgetfields + '   ' +
' From ' + @ Tblname + '   ' + @ Strwhere +
' ) As SP where POS ' + STR (@ pageindex - 1 ) * @ Pagesize + 1 ) + ' And ' + STR (@ pageindex * @ Pagesize)

Exec (@ strsql)

The following is an example of a call. Join join can be used here.

Code Exec [ Proc_showpage ]
@ Tblname   = N ' (Project P inner JOIN project Ca on p. caid = Ca. ID) ' ,
@ Strgetfields   = N ' P. Title, Ca. caname ' ,
@ Strorder   = N ' P. ID ' ,
@ Strordertype   = N ' Desc ' ,
@ Pagesize   =   12 ,
@ Pageindex   =   1 ,
@ Strwhere   = N ''