mssqlserver存储过程分页

USE [BeyondDB]

GO

/****** Object: StoredProcedure [dbo].[Y_Paging] Script Date: 02/22/2013 14:53:26 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER proc [dbo].[Y_Paging]

(

@TableName VARCHAR(max)=null,--表名

@FieldList VARCHAR(max)=null, --显示列名，如果是全部字段则为*

@PrimaryKey VARCHAR(max)=null, --单一主键或唯一值键

@Where NVARCHAR(max)=null,--查询条件 不含'where'字符，如id>10 and len(userid)>9

@Order VARCHAR(max)=null,--排序 不含'order by'字符mssql分页查询，如id asc,userid desc，必须指定asc或desc

@SortType INT=null,--排序规则 1:正序asc 2:倒序desc 3:多列排序方法

@RecorderCount INT=null,--记录总数 0:会返回总记录

@PageSize INT=null,--每页

输出的记录数

@PageIndex INT=null,--当前页数

@Keyword varchar(max)=null,--关键字

@FieldOne varchar(max)=null,--字段1

@FieldTwo varchar(max)=null,--字段2

@TotalCount INT OUTPUT,--记返回总记录

@TotalPageCount INT OUTPUT --返回总页数

)

as

begin

DECLARE @sql NVARCHAR(max);

DECLARE @totalSql NVARCHAR(max);

if(@Keyword is not null and @Keyword !='')

begin

if ISNULL(@FieldOne,'') != ''

set @Order=@Order+' , (case when charindex('''+replace(@Keyword,' ',''','+@FieldOne+')>0 then

1 else 0 end)+(case when charindex(''')+''','+@FieldOne+')>0 then 1 else 0 end) '

if ISNULL(@FieldOne,'') != ''

set @Order=@Order+' , (case when charindex('''+replace(@Keyword,' ',''','+@FieldTwo+')>0 then 1

else 0 end)+(case when charindex(''')+''','+@FieldTwo+')>0 then 1 else 0 end) '

end

if(@SortType is not null and @SortType=1)

set @Order=@Order+' asc '

if(@SortType is not null and @SortType=2)

set @Order=@Order+' desc '

SET @sql = ' WITH LIST AS

(

SELECT ' + @FieldList + ',ROW_NUMBER() OVER (ORDER BY ' + @Order + ') as RowNumber

FROM ' + @TableName + '

WHERE 1=1 ' + @Where + '

)

SELECT * FROM LIST WHERE RowNumber BETWEEN ' + STR(@PageIndex+1) + ' AND ' + STR

(@PageIndex + @PageSize)

set @totalSql = ' SELECT @TOTALCOUNT=COUNT(*) FROM ' + @TableName + ' WHERE 1=1 ' + @Where

print(@Sql)

EXEC(@Sql)

--EXEC sp_executesql @totalSql,N'

--@ID uniqueidentifier,

--@StatusList varchar(max),

--@BeginTime datetime,

--@EndTime datetime,

--@TitleOrNo varchar(max),

--@Excutor uniqueidentifier,

--@Assignor uniqueidentifier,

--@TotalCount int output

--',

--@ID ,

--@StatusList ,

--@BeginTime ,

--@EndTime ,

--@TitleOrNo ,

--@Excutor,

--@Assignor,

--@TotalCount output

end

--调用实例

USE [BeyondDB]

GO

DECLARE @return_value int,

@TotalCount int,

@TotalPageCount int

EXEC @return_value = [dbo].[Y_Paging]

@TableName = N'Account',

@FieldList = N'*',

@PrimaryKey = N'id',

@Where = N' and 1=1',

@Order = N' CreateTime ',

@SortType =2,

@PageSize = 5,

@PageIndex = 0,

@RecorderCount = null,

@Keyword = N'1',

@FieldOne = N'Accountname',

@FieldTwo = N'accountid',

@TotalCount = @TotalCount OUTPUT,

@TotalPageCount = @TotalPageCount OUTPUT

SELECT @TotalCount as N'@TotalCount',

@TotalPageCount as N'@TotalPageCount'

SELECT 'Return Value' = @return_value

GO

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!