Copy Code code as follows:
/*
Database paging stored procedures, supporting reverse and ascending
Parameter description:
@tablename: for search table names
@tablefield: For the field of the table, the Convention is the primary key of the table,
@where: To search for a table name, to display all records, set to "1=1"
@orderby: Sort search results, such as ORDER by id DESC
@fieldlist: For field lists, such as UserID, username
@curpage: Current page number
@page_record: Number of record bars per page
@Sort: Sort identity (if it is in reverse order, parameter value is desc, ascending, and the value of the parameter is ASC, corresponding to the by-order parameter)
Results: Returns the Page_record record of page curpage in table tablename that satisfies the condition where the result is sorted by order
*/
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)--max/min 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 ' + @table Field+ '
' + @op + ' (SELECT ' + @max_min + ' (' + @tablefield + ') from (select Top ' +cast (@uprecord as NVARCHAR) + "+ @tablefield + ' from ' +@t Ablename+ ' WHERE
' + @where + ' + @orderby + ') as Tmptbl) and ' + @where + ' + @orderby
SET @cmd = @cmd + '; SELECT COUNT (*) from ' + @tablename + ' WHERE ' + @where
EXEC (@cmd)
PRINT (@cmd)
End
Go
