创意程序的开创者

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: StoredProcedu本文来源gao@!dai!ma.com搞$$代^@码网*re [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,–表名@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 beginDECLARE @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) ‘ endif(@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 RowNumberFROM ‘ + @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 ‘ + @Whereprint(@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 outputend–调用实例USE [BeyondDB]GODECLARE @return_value int, @TotalCount int, @TotalPageCount intEXEC @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 OUTPUTSELECT @TotalCount as N’@TotalCount’, @TotalPageCount as N’@TotalPageCount’SELECT ‘Return Value’ = @return_valueGO

posted on

喜欢 (0)赏

[搞代码]

分享 (0)