Copy Code code as follows:
--based on Max (MIN) ID
CREATE PROC [dbo]. [PROC_SELECT_ID]
@pageindex int=1,--Current pages
@pagesize int=10,--per page size
@tablename VARCHAR = ',--table name
@fields VARCHAR (1000) = ',--a collection of fields for a query
@keyid VARCHAR = ',--primary key
@condition NVARCHAR (1000) = ',--query criteria
@orderstr VARCHAR,--Sorting criteria
Total records of @totalRecord BIGINT output--
As
IF ISNULL (@orderstr, N ') =n ' SET @orderstr =n ' ORDER by ' + @keyid +n ' DESC '
IF ISNULL (@fields, N ') =n ' SET @fields =n ' * '
IF ISNULL (@condition, N ') =n ' SET @condition =n ' 1=1 '
DECLARE @sql NVARCHAR (4000)
--if (@totalRecord is NULL)
--begin
SET @sql =n ' SELECT @totalRecord =count (*) '
+n ' from ' + @tablename
+n ' WHERE ' + @condition
EXEC sp_executesql @sql, N ' @totalRecord INT output ', @totalRecord output
--end
IF (@pageindex =1)
BEGIN
SET @sql =n ' SELECT top ' +str (@pagesize) +n ' + @fields +n ' from ' + @tablename +n ' WHERE ' + @condition +n ' + @orderstr
EXEC (@sql)
End
ELSE
BEGIN
DECLARE @operatestr char (3), @comparestr char (1)
SET @operatestr = ' MAX '
SET @comparestr = ' > '
IF (@orderstr <> ")
BEGIN
IF (CHARINDEX (' desc ', LOWER (@orderstr)) <>0)
BEGIN
SET @operatestr = ' MIN '
SET @comparestr = ' < '
End
End
SET @sql =n ' SELECT top ' +str (@pagesize) +n ' + @fields +n ' from ' + @tablename +n ' WHERE ' + @keyid + @comparestr
+n ' (SELECT ' + @operatestr +n ' (' + @keyid +n ') from ' + @tablename +n ' WHERE ' + @keyid
+n ' In (@pageindex-1) * @pagesize) +n "+ @keyid +n ' from ' + @tablename +n ' WHERE '
+ @condition +n ' + @orderstr +n ')) and ' + @condition +n ' + @orderstr
EXEC (@sql)
End
Go
--According to Row_number () over
CREATE PROC [dbo]. [Proc_select_page_row]
@pageindex int=1,--Current pages
@pagesize int=10,--per page size
@tablename VARCHAR = ',--table name
@fields VARCHAR (1000) = ' * ',--a collection of fields for a query
@keyid VARCHAR = ',--primary key
@condition NVARCHAR (1000) = ',--query criteria
@orderstr VARCHAR,--Sorting criteria
Total records of @totalRecord BIGINT output--
As
IF ISNULL (@orderstr, N ') =n ' SET @orderstr =n ' ORDER by ' + @keyid +n ' DESC '
IF ISNULL (@fields, N ') =n ' SET @fields =n ' * '
IF ISNULL (@condition, N ') =n ' SET @condition =n ' 1=1 '
DECLARE @sql NVARCHAR (4000)
--IF @totalRecord is NULL
--BEGIN
SET @sql =n ' SELECT @totalRecord =count (*) '
+n ' from ' + @tablename
+n ' WHERE ' + @condition
EXEC sp_executesql @sql, N ' @totalRecord bigint output ', @totalRecord output
--end
IF (@pageindex =1)
BEGIN
SET @sql =n ' SELECT top ' +str (@pagesize) +n ' + @fields +n ' from ' + @tablename +n ' WHERE ' + @condition +n ' + @orderstr
EXEC (@sql)
End
ELSE
BEGIN
DECLARE @StartRecord INT
SET @StartRecord = (@pageindex-1) * @pagesize + 1
SET @sql =n ' select * FROM (select Row_number () over (' + @orderstr +n ') as RowId, ' + @fields +n ' from ' + @tablename +n ') as T WH ERE rowid>= ' +str (@StartRecord) +n ' and rowid<= ' +str (@StartRecord + @pagesize-1)
EXEC (@sql)
End
Go
--based on top ID
CREATE PROC [dbo]. [Proc_select_page_top]
@pageindex int=1,--Current pages
@pagesize int=10,--per page size
@tablename VARCHAR = ',--table name
@fields VARCHAR (1000) = ',--a collection of fields for a query
@keyid VARCHAR = ',--primary key
@condition NVARCHAR (1000) = ',--query criteria
@orderstr VARCHAR,--Sorting criteria
Total records of @totalRecord BIGINT output--
As
IF ISNULL (@orderstr, N ') =n ' SET @orderstr =n ' ORDER by ' + @keyid +n ' DESC '
IF ISNULL (@fields, N ') =n ' SET @fields =n ' * '
IF ISNULL (@condition, N ') =n ' SET @condition =n ' 1=1 '
DECLARE @sql NVARCHAR (4000)
--if (@totalRecord is NULL)
--begin
SET @sql =n ' SELECT @totalRecord =count (*) '
+n ' from ' + @tablename
+n ' WHERE ' + @condition
EXEC sp_executesql @sql, N ' @totalRecord INT output ', @totalRecord output
--end
IF (@pageindex =1)
BEGIN
SET @sql =n ' SELECT top ' +str (@pagesize) +n ' + @fields +n ' from ' + @tablename +n ' WHERE ' + @condition +n ' + @orderstr
EXEC (@sql)
End
ELSE
BEGIN
SET @sql =n ' SELECT top ' +str (@pagesize) +n ' + @fields +n ' from ' + @tablename +n ' WHERE ' + @keyid
+n ' +str ((@pageindex-1) * @pagesize) +n ' + @keyid +n ' from '
+ @tablename +n ' WHERE ' + @condition +n ' + @orderstr +n ') and ' + @condition +n ' + @orderstr
EXEC (@sql)
End
Go
