主键列分页的动态存储过程

USE [Northwind]

GO

 

/****** Object:  StoredProcedure [dbo].[SP_PageIndex]    Script Date: 01/13/2014 23:22:34 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE proc  [dbo].[SP_PageIndex]

@DBname    nvarchar(50),--库名

@tablename nvarchar(50),--表名

@Tid       nvarchar(50),--主键列

@PageSiz   int,         --每页多少条数据

@Page      int          --当前页

as

declare

@sql nvarchar(1000)--sql语句

begin

  set

   @sql='select top '+convert(nvarchar(50),@PageSiz)+'

   *

  from  '+convert(nvarchar(50),@DBname)+'.dbo.'+CAST(@tablename as nvarchar(50))+'

  where '+@Tid+' 

  not in

  (

   select top '+convert(nvarchar(50),(@PageSiz*@Page))+'

   '+@Tid+'

   from

   '+convert(nvarchar(50),@DBname)+'.dbo.'+CAST(@tablename as nvarchar(50))+'

   order by '+@Tid+'

  )

  order by '+@Tid+''

  exec(@sql)

end

 

GO

 

--------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------以sql2008自带notthwind为例子-----------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------------

---------执行     exec SP_PageIndex  Northwind,Employees,EmployeeID,2,3