CREATE PROCEDURE [dbo].[GetUsers]
@RowIndex int,----當前指定的頁數
@RecordCount int----每頁顯示的記錄數
AS
BEGIN
SET NOCOUNT ON;
With VUsers as (
select *,row_number() over (order by UserID desc) as RowNum
from MyUsers
)
select * from VUsers
where RowNum > @RowIndex and RowNum <= (@RowIndex+@RecordCount)
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Alter PROCEDURE UP_GetRecordByPage
@tblName varchar(255), -- 表名
@keyName varchar(255), --主键
@PageSize int = 10, -- 页尺寸
@PageIndex int = 0 , -- 页码
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @beginItemIndex int --开始项
declare @endItemIndex int --结束项
set @beginItemIndex=@PageIndex*@PageSize
set @endItemIndex=@beginItemIndex+@PageSize
set @strSQl='Select top 10 * FROM
(
select *,
ROW_NUMBER() OVER( orDER BY '+@keyName+' DESC ) as rowNo
from '
set @strSQL=@strSQL+@tblName+' where 1=1 '
if @strWhere!=''
begin
set @strSQL=@strSQL+@strWhere;
end
set @strSQL=@strSQL+')
AS tabeTemp
Where RowNo >='+cast (@beginItemIndex as varchar ) +' and RowNo <='+ cast (@endItemIndex as