SQL Server经典的分页存储过程
在开发当中，我经常需要分页，而下面这个存储过程的分页很强大，可以对于任何表都可以做到分页。--创建
create proc Ture_Page
go
--修改
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE Ture_Page
@PageSize int, --每页的行数
@PageIndex int, --1 代表第一页
@Col varchar(250), --要显示的字段
@Table varchar(200), --所用到的表,复条的话就写from与where之间的内容
@Where varchar(200)='', --所用到的条件
@OKey varchar(50), --排序字段
@Order varchar(20)='ASC' --排序方式
as
declare @cmdstr varchar(2000)
declare @cmdstrcount varchar(2000)
set nocount on
set @cmdstr='select top '
set @cmdstr=@cmdstr+convert(nvarchar,@PageSize)
if @Order='DESC' and @PageIndex>1
set @cmdstr=@cmdstr+' '+@Col+' from '+@Table+' where '+@OKey+'<'
else if @PageIndex=1
set @cmdstr=@cmdstr+' '+@Col+' from '+@Table+' where '+@OKey+'>='
else
set @cmdstr=@cmdstr+' '+@Col+' from '+@Table+' where '+@OKey+'>'
if @PageIndex>1
begin
if @Order='ASC'
set @cmdstr=@cmdstr+'(select max ('+@OKey+') from (select top '
else
set @cmdstr=@cmdstr+'(select min ('+@OKey+') from (select top '
set @cmdstr=@cmdstr+convert(nvarchar,(@PageIndex-1)*@PageSize)
if @Where<>''
set @cmdstr=@cmdstr+' '+@OKey+' from '+@Table+' where '+@Where+' order by '+@OKey+' '+@Order+') as t) '
else
set @cmdstr=@cmdstr+' '+@OKey+' from '+@Table+' order by '+@OKey+' '+@Order+') as t) '
end
else
set @cmdstr=@cmdstr+' 0 ' --convert(nvarchar,0)
print @cmdstr
if @Where<>''
set @cmdstr=(@cmdstr+' and '+@Where+' order by '+@OKey+' '+@Order)
else
set @cmdstr=(@cmdstr+' order by '+@OKey+' '+@Order)
print @cmdstr
exec(@cmdstr)
set nocount off
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
带字符串的条件参数的传递： 'c_type= ' ' “+参数+”' ' '
页:
[1]