CREATE PROCEDURE GetRecordByPage ( @sTable nvarchar(100), --表名 @sPkey nvarchar(50), --主键(一定要有) @sField nvarchar(1000)='*', --字段 @iPageCurr int, --当前页数 @iPageSize int, --每页记录数 @sCondition nvarchar(1000), --条件(不需要where) @sOrder nvarchar(100) , --排序(不需要order by,需要asc和desc字符) @Counts int=0 output, --记录条数(已有值:外部赋值,0执行count) @pageCount int=1 output --查询结果分页后的总页数 ) AS SET NOCOUNT ONDECLARE @sC1 nvarchar(1000),@sC2 nvarchar(1000) DECLARE @iAsc int,@iDesc int,@iType tinyint DECLARE @sT1 nvarchar(1000),@sT2 nvarchar(1000),@sT3 nvarchar(1000),@sT4 nvarchar(1000),@sSQL nvarchar(4000)/*----------------------判断where 条件是否空值-------------------*/ IF LEN(@sCondition)>2 SELECT @sC1=' WHERE '+@sCondition+' ', @sC2=' WHERE '+@sCondition+' AND ' ELSE BEGIN SELECT @sC1='', @sC2=' WHERE ' END SELECT @sT1=UPPER(@sOrder), @sT2=@sOrder, @iType=0, @sOrder='', @sT4=UPPER(@sPkey)/*-----------------------获取查询的数据行数---------------------*/ IF LEN(@sT2)>2 BEGIN SELECT @iAsc=0, @iDesc=0 IF @sT4=SUBSTRING(@sT1,0,LEN(@sT4)) --存在主建 BEGIN SELECT @iAsc=CHARINDEX('ASC',@sT1), @iDesc=CHARINDEX('DESC',@sT1) END IF (@iAsc>0 and @iDesc=0) OR ((@iAsc>0 AND @iDesc>0) AND (@iAsc<@iDesc)) SELECT @iType=1, @sT3='>(SELECT MAX(' ELSE IF (@iAsc=0 and @iDesc>0) OR ((@iAsc>0 AND @iDesc>0) AND (@iAsc>@iDesc)) BEGIN SELECT @iType=1, @sT3='<(SELECT MIN(' END SET @sOrder=' ORDER BY '+@sT2 END*-------------------------获取查询的数据行数----------------------*/--IF (@Counts<1) --BEGIN --SET @sSQL='SELECT @Counts=Count(0) FROM '+@sTable+@sC1 --EXEC sp_executesql @sSQL,N'@Counts int OUT',@Counts OUT --ENDSET @sSQL='SELECT @Counts=Count(0) FROM '+@sTable+@sC1 EXEC sp_executesql @sSQL,N'@Counts int OUT',@Counts OUTSET @pageCount=(@Counts+@ipageSize-1)/@ipageSizeIF @iPageCurr>@pageCount SET @iPageCurr=@pageCountSELECT @iPageCurr=(CASE WHEN @Counts<(@iPageCurr-1)*@iPageSize THEN CEILING(@Counts/@iPageSize) WHEN @iPageCurr<1 THEN 1 ELSE @iPageCurr END)IF (@iPageCurr>1) AND (@iType=1) SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sC2+@sPkey+@sT3+@sPkey+') FROM (SELECT TOP '+CAST((@iPageCurr-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC1+@sOrder+') AS tbTemp)'+@sOrder ELSE IF (@iPageCurr>1) AND (@iType=0) SET @sSQL='SELECT '+@sField+' FROM '+@sTable+@sC2+@sPkey+' IN (SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC2+@sPkey+' NOT IN(SELECT TOP '+CAST((@iPageCurr-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC1+@sOrder+')'+@sOrder+')'+@sOrder ELSE BEGIN SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sC1+@sOrder ENDEXEC(@sSQL)print @sSQL print @Counts print @pageCountSET NOCOUNT OFF GO
孟子 邹健 李洪根 csdn牛人呢 找不到你们了 你们的分页是用那个存储呢
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROC [dbo].[PROCE_PageView2000] ( @tbname nvarchar(100), --要分页显示的表名 @FieldKey nvarchar(1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段 @PageCurrent int=1, --要显示的页码 @PageSize int=10, --每页的大小(记录数) @FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段 @FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC @WhereString nvarchar(1000)=N'', --查询条件 @RecordCount int output --总记录数 ) AS SET NOCOUNT ON --检查对象是否有效 --IF OBJECT_ID(@tbname) IS NULL --BEGIN -- RAISERROR(N'对象"%s"不存在',1,16,@tbname) -- RETURN --END --IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0 -- AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0 -- AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0 --BEGIN -- RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname) -- RETURN --END--分页字段检查 IF ISNULL(@FieldKey,N'')='' BEGIN RAISERROR(N'分页处理需要主键(或者惟一键)',1,16) RETURN END--其他参数检查及规范 IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1 IF ISNULL(@PageSize,0)<1 SET @PageSize=10 IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*' IF ISNULL(@FieldOrder,N'')=N'' SET @FieldOrder=N'' ELSE SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder) IF ISNULL(@WhereString,N'')=N'' SET @WhereString=N'' ELSE SET @WhereString=N'WHERE ('+@WhereString+N')'--如果@RecordCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@RecordCount赋值) IF @RecordCount IS NULL BEGIN DECLARE @sql nvarchar(4000) SET @sql=N'SELECT @RecordCount=COUNT(*)' +N' FROM '+@tbname +N' '+@WhereString EXEC sp_executesql @sql,N'@RecordCount int OUTPUT',@RecordCount OUTPUT END--计算分页显示的TOPN值 DECLARE @TopN varchar(20),@TopN1 varchar(20) SELECT @TopN=@PageSize, @TopN1=(@PageCurrent-1)*@PageSize--第一页直接显示 IF @PageCurrent=1 EXEC(N'SELECT TOP '+@TopN +N' '+@FieldShow +N' FROM '+@tbname +N' '+@WhereString +N' '+@FieldOrder) ELSE BEGIN --处理别名 IF @FieldShow=N'*' SET @FieldShow=N'a.*' --生成主键(惟一键)处理条件 DECLARE @Where1 nvarchar(4000),@Where2 nvarchar(4000), @s nvarchar(1000),@Field sysname SELECT @Where1=N'',@Where2=N'',@s=@FieldKey WHILE CHARINDEX(N',',@s)>0 SELECT @Field=LEFT(@s,CHARINDEX(N',',@s)-1), @s=STUFF(@s,1,CHARINDEX(N',',@s),N''), @Where1=@Where1+N' AND a.'+@Field+N'=b.'+@Field, @Where2=@Where2+N' AND b.'+@Field+N' IS NULL', @WhereString=REPLACE(@WhereString,@Field,N'a.'+@Field), @FieldOrder=REPLACE(@FieldOrder,@Field,N'a.'+@Field), @FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field) SELECT @WhereString=REPLACE(@WhereString,@s,N'a.'+@s), @FieldOrder=REPLACE(@FieldOrder,@s,N'a.'+@s), @FieldShow=REPLACE(@FieldShow,@s,N'a.'+@s), @Where1=STUFF(@Where1+N' AND a.'+@s+N'=b.'+@s,1,5,N''), @Where2=CASE WHEN @WhereString='' THEN N'WHERE (' ELSE @WhereString+N' AND (' END+N'b.'+@s+N' IS NULL'+@Where2+N')' --执行查询 EXEC(N'SELECT TOP '+@TopN +N' '+@FieldShow +N' FROM '+@tbname +N' a LEFT JOIN(SELECT TOP '+@TopN1 +N' '+@FieldKey +N' FROM '+@tbname +N' a '+@WhereString +N' '+@FieldOrder +N')b ON '+@Where1 +N' '+@Where2 +N' '+@FieldOrder) END
(
@sTable nvarchar(100), --表名
@sPkey nvarchar(50), --主键(一定要有)
@sField nvarchar(1000)='*', --字段
@iPageCurr int, --当前页数
@iPageSize int, --每页记录数
@sCondition nvarchar(1000), --条件(不需要where)
@sOrder nvarchar(100) , --排序(不需要order by,需要asc和desc字符)
@Counts int=0 output, --记录条数(已有值:外部赋值,0执行count)
@pageCount int=1 output --查询结果分页后的总页数
)
AS
SET NOCOUNT ONDECLARE @sC1 nvarchar(1000),@sC2 nvarchar(1000)
DECLARE @iAsc int,@iDesc int,@iType tinyint
DECLARE @sT1 nvarchar(1000),@sT2 nvarchar(1000),@sT3 nvarchar(1000),@sT4 nvarchar(1000),@sSQL nvarchar(4000)/*----------------------判断where 条件是否空值-------------------*/
IF LEN(@sCondition)>2
SELECT @sC1=' WHERE '+@sCondition+' ', @sC2=' WHERE '+@sCondition+' AND '
ELSE
BEGIN
SELECT @sC1='', @sC2=' WHERE '
END
SELECT @sT1=UPPER(@sOrder), @sT2=@sOrder, @iType=0, @sOrder='', @sT4=UPPER(@sPkey)/*-----------------------获取查询的数据行数---------------------*/
IF LEN(@sT2)>2
BEGIN
SELECT @iAsc=0, @iDesc=0
IF @sT4=SUBSTRING(@sT1,0,LEN(@sT4)) --存在主建
BEGIN
SELECT @iAsc=CHARINDEX('ASC',@sT1), @iDesc=CHARINDEX('DESC',@sT1)
END IF (@iAsc>0 and @iDesc=0) OR ((@iAsc>0 AND @iDesc>0) AND (@iAsc<@iDesc))
SELECT @iType=1, @sT3='>(SELECT MAX('
ELSE IF (@iAsc=0 and @iDesc>0) OR ((@iAsc>0 AND @iDesc>0) AND (@iAsc>@iDesc))
BEGIN
SELECT @iType=1, @sT3='<(SELECT MIN('
END
SET @sOrder=' ORDER BY '+@sT2
END*-------------------------获取查询的数据行数----------------------*/--IF (@Counts<1)
--BEGIN
--SET @sSQL='SELECT @Counts=Count(0) FROM '+@sTable+@sC1
--EXEC sp_executesql @sSQL,N'@Counts int OUT',@Counts OUT
--ENDSET @sSQL='SELECT @Counts=Count(0) FROM '+@sTable+@sC1
EXEC sp_executesql @sSQL,N'@Counts int OUT',@Counts OUTSET @pageCount=(@Counts+@ipageSize-1)/@ipageSizeIF @iPageCurr>@pageCount
SET @iPageCurr=@pageCountSELECT @iPageCurr=(CASE WHEN @Counts<(@iPageCurr-1)*@iPageSize THEN CEILING(@Counts/@iPageSize) WHEN @iPageCurr<1 THEN 1 ELSE @iPageCurr END)IF (@iPageCurr>1) AND (@iType=1)
SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sC2+@sPkey+@sT3+@sPkey+') FROM (SELECT TOP '+CAST((@iPageCurr-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC1+@sOrder+') AS tbTemp)'+@sOrder
ELSE IF (@iPageCurr>1) AND (@iType=0)
SET @sSQL='SELECT '+@sField+' FROM '+@sTable+@sC2+@sPkey+' IN (SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC2+@sPkey+' NOT IN(SELECT TOP '+CAST((@iPageCurr-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC1+@sOrder+')'+@sOrder+')'+@sOrder
ELSE
BEGIN
SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sC1+@sOrder
ENDEXEC(@sSQL)print @sSQL
print @Counts
print @pageCountSET NOCOUNT OFF GO
邹健
李洪根
csdn牛人呢
找不到你们了
你们的分页是用那个存储呢
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[PROCE_PageView2000]
(
@tbname nvarchar(100), --要分页显示的表名
@FieldKey nvarchar(1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
@WhereString nvarchar(1000)=N'', --查询条件
@RecordCount int output --总记录数
)
AS
SET NOCOUNT ON
--检查对象是否有效
--IF OBJECT_ID(@tbname) IS NULL
--BEGIN
-- RAISERROR(N'对象"%s"不存在',1,16,@tbname)
-- RETURN
--END
--IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
-- AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
-- AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
--BEGIN
-- RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
-- RETURN
--END--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分页处理需要主键(或者惟一键)',1,16)
RETURN
END--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
SET @FieldOrder=N''
ELSE
SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@WhereString,N'')=N''
SET @WhereString=N''
ELSE
SET @WhereString=N'WHERE ('+@WhereString+N')'--如果@RecordCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@RecordCount赋值)
IF @RecordCount IS NULL
BEGIN
DECLARE @sql nvarchar(4000)
SET @sql=N'SELECT @RecordCount=COUNT(*)'
+N' FROM '+@tbname
+N' '+@WhereString
EXEC sp_executesql @sql,N'@RecordCount int OUTPUT',@RecordCount OUTPUT
END--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
@TopN1=(@PageCurrent-1)*@PageSize--第一页直接显示
IF @PageCurrent=1
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' '+@WhereString
+N' '+@FieldOrder)
ELSE
BEGIN
--处理别名
IF @FieldShow=N'*'
SET @FieldShow=N'a.*' --生成主键(惟一键)处理条件
DECLARE @Where1 nvarchar(4000),@Where2 nvarchar(4000),
@s nvarchar(1000),@Field sysname
SELECT @Where1=N'',@Where2=N'',@s=@FieldKey
WHILE CHARINDEX(N',',@s)>0
SELECT @Field=LEFT(@s,CHARINDEX(N',',@s)-1),
@s=STUFF(@s,1,CHARINDEX(N',',@s),N''),
@Where1=@Where1+N' AND a.'+@Field+N'=b.'+@Field,
@Where2=@Where2+N' AND b.'+@Field+N' IS NULL',
@WhereString=REPLACE(@WhereString,@Field,N'a.'+@Field),
@FieldOrder=REPLACE(@FieldOrder,@Field,N'a.'+@Field),
@FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field)
SELECT @WhereString=REPLACE(@WhereString,@s,N'a.'+@s),
@FieldOrder=REPLACE(@FieldOrder,@s,N'a.'+@s),
@FieldShow=REPLACE(@FieldShow,@s,N'a.'+@s),
@Where1=STUFF(@Where1+N' AND a.'+@s+N'=b.'+@s,1,5,N''),
@Where2=CASE
WHEN @WhereString='' THEN N'WHERE ('
ELSE @WhereString+N' AND ('
END+N'b.'+@s+N' IS NULL'+@Where2+N')' --执行查询
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' a LEFT JOIN(SELECT TOP '+@TopN1
+N' '+@FieldKey
+N' FROM '+@tbname
+N' a '+@WhereString
+N' '+@FieldOrder
+N')b ON '+@Where1
+N' '+@Where2
+N' '+@FieldOrder)
END