sql2005分页存储过程 和 sql2000分页存储过程 及使用方法

sql2005分页存储过程及使用方法

-------------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[GetRecordFromPage]
@tblName      varchar(255),       -- 表名
@sortName      varchar(255),      -- 字段名
@fldNames     varchar(1000),      -- 返回字段
@PageSize     int = 10,           -- 页尺寸
@PageIndex    int,                -- 页码
@IsCount      bit = 1,            -- 是否返回记录总数。1：返回
@strWhere     varchar(2000) = ''  -- 查询条件 (注意: 不要加 where) 
AS

declare @strSQL   varchar(5000)

if @strWhere !=''
set @strWhere=' where '+@strWhere

set @strSQL='SELECT * FROM (SELECT ROW_NUMBER() OVER (order by '+@sortName+') AS pos,'+@fldNames+' FROM '+@tblName+''+@strWhere+') AS sp WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)

--set @strSQL='WITH TempTable AS ( SELECT ROW_NUMBER() OVER (ORDER BY '+ @sortName +')AS Row, '+ @fldNames +' from '+ @tblName + @strWhere +')'
--set @strSQL=@strSQL+'SELECT * FROM TempTable where Row between '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize);

 

 --set @strSQL='WITH TempTable AS ( SELECT ROW_NUMBER() OVER (ORDER BY '+ @KeyWordAndOrder +')AS Row, '+ @fieldName +' from '+ @tables + @tj +')'
 --set @strSQL=@strSQL+'SELECT * FROM TempTable where Row between '+convert(nvarchar,@startIndex)+' and '+convert(nvarchar,@endIndex)+'';

print @strSQL

exec (@strSQL)
if(@IsCount!=0)
begin
set   @strSQL='SELECT 1 FROM '+@tblName+' '+@strWhere
exec (@strSQL)
return @@ROWCOUNT
end

-----------------------------------------------

使用方法:

DataTable GetDataTable(string tableName, string FieldName, string strWhere, string orderBy, int PageSize, int currentPageIndex, ref int rowCount)

 

           SqlParameter[] parameters = new SqlParameter[] {
     new SqlParameter("@tblName", SqlDbType.VarChar),
     new SqlParameter("@sortName", SqlDbType.VarChar),
                    new SqlParameter("@fldNames", SqlDbType.VarChar),
     new SqlParameter("@PageSize", SqlDbType.Int),
     new SqlParameter("@PageIndex", SqlDbType.Int),
     new SqlParameter("@IsCount", SqlDbType.Bit),
     new SqlParameter("@strWhere", SqlDbType.VarChar),
                    new SqlParameter("@rowCount",SqlDbType.Int)
     };
           parameters[0].Value = tableName;
           parameters[1].Value = sortFieldName;
           parameters[2].Value = filedNames;
           parameters[3].Value = pageSize;
           parameters[4].Value = currentPageIndex;
           parameters[5].Value = 1;
           parameters[6].Value = whereStr;
           parameters[7].Direction =  ParameterDirection.ReturnValue;

           获取参数

          rowCount = Convert.ToInt32(parameters[7].Value);

-----------------------------------------------------------------------------------------------

sql2000分页存储过程 及使用方法

==