创意程序的开创者

View Post 存储过程分页 USE [BeyondDB]GO/****** Object: StoredProcedure [dbo].[Y_Paging] Script Date: 02/22/2013 14:53:26 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc [dbo].[Y_Paging](@TableName VARCHAR(max)=null,--表名@Fi

View Post

存储过程分页

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 ', @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

posted on