用过的分页存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_paging] (
@Tname varchar(255),/*表名*/
@kname varchar(255),/*主键名*/
@columns varchar(500),/*select的列名*/
@whereText varChar(500),/*查询条件*/
@orderText varChar(100),/*排序条件*/
@startIndex int,/*取的数目开始值*/
@endIndex int/*取的数目的结束值*/
)
AS
BEGIN
    SET NOCOUNT ON;
    declare @sqlstring varchar(1000)
    set rowcount @endIndex
    DECLARE @tableid varchar(20)
    DECLARE @idType varchar(20)
    select @tableid=id   from sysobjects where name=@Tname
    select @idType=xtype from sysColumns where id=@tableid and LOWER(name)='id'
    /*如果主键Id为varchar时,则临时表的nid为varchar,否则为int*/
    if @idType='167'
    begin
        /*得到一张有顺序Id的临时表,用户分页*/
        select @sqlstring = 'declare @indextable table(id int identity(1,1),nid varchar(50))
insert into @indextable(nid) 
select '+@kname+' from '+@Tname+' where '+@whereText+' order by '+@orderText  
+' select '+@columns+' from '+@Tname+' t, @indextable o where t.'+@kname+' =o.nid 
and o.id between '+convert(varchar,@startIndex)  
+' and  '+convert(varchar,@endIndex) +' order by o.id'
    end
    else
    begin
        select @sqlstring = 'declare @indextable table(id int identity(1,1),nid int)
insert into @indextable(nid) 
select '+@kname+' from '+@Tname+' where '+@whereText+' order by '+@orderText  
+' select '+@columns+' from '+@Tname+' t, @indextable o where t.'+@kname+' =o.nid 
and o.id between '+convert(varchar,@startIndex)  
+' and  '+convert(varchar,@endIndex) +' order by o.id'
    end


    exec(@sqlstring)

    SET NOCOUNT OFF;
END