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’字符,如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 ‘, 本文来源gaodaimacom搞#^代%!码网@ @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
|