sql server 纯sql语句分页 存储过程 返回结果集及页总数等

经常要对数据库中的数据进行大量的查询，特别是业务后台，但是EF等提供的语句应用起来可能看起来复杂、臃肿，我们需要借助SQL语句、或者存储过程来取得结果

1、.net EF 定义参数

 using (YouEntites db = new YouEntites())            {                var pageIndex = new System.Data.SqlClient.SqlParameter                {                    ParameterName = "@pageIndex",                    Value = pageNum                };                var pageSize = new System.Data.SqlClient.SqlParameter                {                    ParameterName = "@pageSize",                    Value = rowPerPage                };                var userid = new System.Data.SqlClient.SqlParameter                {                    ParameterName = "@userid",                    Value = userId                };                var totalRecord = new SqlParameter                {                    ParameterName = "@totalRecord",                    Value = recordCount,                    Direction = ParameterDirection.Output                    //Direction = ParameterDirection.ReturnValue;                };                var TotalPage = new SqlParameter                {                    ParameterName = "@TotalPage",                    Value = totalPage,                    Direction = ParameterDirection.Output                    //Direction = ParameterDirection.ReturnValue;                };                var start = new System.Data.SqlClient.SqlParameter                {                    ParameterName = "@start",                    Value = startTime                };                var end = new System.Data.SqlClient.SqlParameter                {                    ParameterName = "@end",                    Value = endTime                };                System.Data.SqlClient.SqlParameter[] parm = { userid, totalRecord, TotalPage, start, end, pageSize, pageIndex };                try                {                    var results = db.Database.SqlQuery<Cost>("exec Query @userid,@totalRecord  Output, @TotalPage Output,@start,@end,@pageSize,@pageIndex", parm);                    List<Cost> list = results.ToList();                    recordCount = Convert.ToInt32(totalRecord.Value);                    totalPage = Convert.ToInt32(TotalPage.Value);                    return list;                }                catch (Exception ex)                {                    Console.Write(ex.Message);                }

值得注意的是

 var TotalPage = new SqlParameter                {                    ParameterName = "@TotalPage",                    Value = totalPage,                    Direction = ParameterDirection.Output                    //Direction = ParameterDirection.ReturnValue;                };

此参数为引用参数，注意！相当于一个存储过程要传出多个变量（.NET EF code first有个缺陷，不能返回多个返回集，但是可以返回除一个返回查询集以外的多个引用参数；但是DB First ，EF自动生成的模型edmx的时候是可以取得多个查询集的）

2、存储过程：

CREATE PROCEDURE [dbo].[Query]@userid int, @totalRecord int OUTPUT, @TotalPage int OUTPUT, @start datetime, @end datetime, @pageSize int, @pageIndex intWITH EXEC AS CALLERASbeginselect @totalRecord=count(*) from Cost where Users_ids in(@userid) and BackTime between @start and @end and InSuccess=1 and PaySuccess=1;--计算总页数select @TotalPage=CEILING((@totalRecord+0.0)/@pageSize)--处理页数超出范围情况    if @pageIndex<=0        Set @pageIndex = 1;    if @pageIndex>@TotalPage        Set @pageIndex = @TotalPage;     --处理开始点和结束点    Declare @StartRecord int;    Declare @EndRecord int;    set @StartRecord = (@pageIndex-1)*@PageSize + 1    set @EndRecord = @StartRecord + @pageSize - 1select * from( select * ,ROW_NUMBER() over(order by BackTime desc) as "No."  from cost where Users_ids in(@userid) --后接其它条件)  as t  where t."No." between @StartRecord and @EndRecord  endGO

注意分页的处理

3、关于分页的一些引述

常用的分页方式有：

select top 和select not in
select top 和 select max(列键)
select top和中间变量
利用Row_number() 此方法
利用临时表及Row_number
所非本人的测试，其针对较大量的数据其效果表现如下：

测试结果显示：select max >row_number>not in>临时表>中间变量

一般选择row_number方式。

参见：

http://q.cnblogs.com/q/56836/

http://www.cnblogs.com/lli0077/archive/2008/09/03/1282862.html

https://technet.microsoft.com/zh-cn/library/ms187004(v=sql.105).aspx