效果圖：

下面是存儲(chǔ)過程的創(chuàng)建，用的時(shí)候調(diào)用就行了

/****** Object:  StoredPRocedure [dbo].[spSqlPageByRownumber]    Script Date: 2015/3/5 17:34:38 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE proc [dbo].[spSqlPageByRownumber]@tbName varchar(255),        --表名@tbFields varchar(1000),      --返回字段@PageSize int,                --頁尺寸@PageIndex int,                --頁碼@strWhere varchar(1000),    --查詢條件@StrOrder varchar(255)  --排序條件asdeclare @strSql varchar(5000)    --主語句declare @strSqlCount nvarchar(500)----------------總記錄數(shù)---------------if @strWhere !=''beginset @strSqlCount='Select count(*) as TotalCout from  ' + @tbName + ' where '+ @strWhereendelsebeginset @strSqlCount='Select count(*) as TotalCout from  ' + @tbNameend--------------分頁------------if @PageIndex <= 0begin  set @PageIndex = 1endif @strWhere !=''beginset @strSql='Select * from (Select  row_number() over('+@strOrder+') rowId,'+ @tbFields+' from ' + @tbName + ' where  ' + @strWhere+' ) tb where tb.rowId >'+str((@PageIndex-1)*@PageSize)+' and tb.rowId <= ' +str(@PageIndex*@PageSize)endelsebeginset @strSql='Select * from (Select  row_number() over('+@strOrder+') rowId,'+ @tbFields+' from ' + @tbName + ' ) tb where tb.rowId >'+str((@PageIndex-1)*@PageSize)+' and tb.rowId <= ' +str(@PageIndex*@PageSize)endexec(@strSqlCount)exec(@strSql)GO

執(zhí)行：

exec [dbo].spSqlPageByRownumber 'GoodsCategory','*',10,2,'Cid > 0','order by Cid desc'