分页(带参数)

create procedure Page

@tablename varchar(50),@primarykey varchar(50),@pageindex int,@pagesize int,@where varchar(1000)

as

 

    declare @strsql varchar(5000);

    begin

       if(@where='')

       begin

           set @strsql='select top '+cast(@pagesize as varchar(50))+' * from '+@tablename+' where '+@primarykey+' not in (select top '+cast(@pagesize*(@pageindex-1) as varchar(50))+' '+@primarykey+' from '+@tablename+' order by '+@primarykey+') order by '+@primarykey

       end

       else

       begin

           set @strsql='select top '+cast(@pagesize as varchar(50))+' * from '+@tablename+' where '+@where+' and '+@primarykey+' not in (select top '+cast(@pagesize*(@pageindex-1) as varchar(50))+' '+@primarykey+' from '+@tablename+' where '+@where+' order by '+@primarykey+') order by '+@primarykey

       end

    end

exec (@strsql)

go

 

execute Page 'Student','studentid',1,2,'studentname=''咩咩'''

go

 

注意：这个要执行那个execute (@strsql)；还有一个就是要空格；最后一个是执行存储过程时候的引号'studentname=''咩咩'''s