Database paging Stored Procedure Code

Database paging Stored Procedure Code

Database paging Stored Procedure Code

The Code is as follows:
/*
Database paging stored procedures, supporting reverse and ascending
Parameter description:
@ Tablename: name of the search table
@ Tablefield: Specifies the table field, which is the table's primary key,
@ Where: The name of the search table. to display all records, set it to "1 = 1"
@ Orderby: sorts search results, for example, order by id desc
@ Fieldlist: List of fields, such as userid and username
@ Curpage: Current page number
@ Page_record: number of records per page
@ Sort: Sorting identifier (for reverse sorting, the parameter value is desc, Which is ascending, and the parameter value is asc, which corresponds to the orderby parameter)
Result: The page_record records on the page curpage that meet the where condition in the tablename table are returned. The results are ordered by orderby.
*/
Create procedure proc_CommonPaging

@ Tablename varchar (100 ),
@ Tablefield varchar (20 ),
@ Where varchar (5000 ),
@ Orderby varchar (500 ),
@ Fieldlist varchar (1000 ),
@ Curpage int,
@ Page_record int,
@ Sort varchar (8)

AS

BEGIN

DECLARE @ cmd varchar (8000)
DECLARE @ uprecord int
DECLARE @ Op varchar (2) -- operator
DECLARE @ max_min varchar (4) -- Maximum/minimum calculation

SET @ op = '<'
SET @ max_min = 'Min'
IF @ sort = 'asc'
BEGIN
SET @ Op = '>'
SET @ max_min = 'Max'
END

SET @ uprecord = @ curpage * @ page_record

IF @ curpage = 0
SET @ cmd = 'select TOP '+ cast (@ page_record as nvarchar) + ''+ @ fieldlist + 'from' + @ tablename + 'where' + @ WHERE +'' + @ orderby
ELSE
SET @ cmd = 'select TOP '+ cast (@ page_record as nvarchar) + ''+ @ fieldlist + 'from' + @ tablename + 'where' + @ WHERE + 'and' + @ tablefield +'
'+ @ Op +' (SELECT '+ @ max_min +' ('+ @ tablefield +') FROM (select top '+ cast (@ uprecord as nvarchar) + ''+ @ tablefield + 'from' + @ tablename + 'where
'+ @ Where + ''+ @ orderby +') AS TmpTbl) AND '+ @ where +'' + @ orderby

SET @ cmd = @ cmd + '; select count (*) FROM' + @ tablename + 'where' + @ WHERE

EXEC (@ cmd)
PRINT (@ cmd)

END
GO