/// <summary>
/// 将已经存在的SQL语句包装成分页SQL存储过程。
/// </summary>
/// <param name="sourceSql">已经存在的SQL语句。</param>
/// <param name="key">对于已经存在的SQL语句中能唯一标识的字段名称。</param>
/// <param name="keyType">对于key在SQL中的类型名称。</param>
/// <param name="startRowIndex">分页起始记录索引号。</param>
/// <param name="maximumRows">每页最大记录数。</param>
/// <returns>分页SQL存储过程语句。</returns>
public static string WrapPagingProcedure(string sourceSql, string key, string keyType, int? startRowIndex, int? maximumRows)
{
if (startRowIndex == null || maximumRows == null)
{
return sourceSql;
}
startRowIndex++;
string sql =
"DECLARE @PK " + keyType + " " +
"DECLARE @tblPK TABLE(PK " + keyType + " NOT NULL PRIMARY KEY) " +
"DECLARE @PagingSize int " +
"DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR " +
"SELECT " + key + " FROM(" + sourceSql + ") AS T " +
"OPEN PagingCursor " +
"FETCH RELATIVE " + startRowIndex.ToString() + " FROM PagingCursor INTO @PK " +
"SET @PagingSize = " + maximumRows.ToString() + " " +
"WHILE @PagingSize > 0 AND @@FETCH_STATUS = 0 " +
"BEGIN " +
" INSERT @tblPK(PK) VALUES(@PK) " +
" FETCH NEXT FROM PagingCursor INTO @PK " +
" SET @PagingSize = @PagingSize - 1 " +
"END " +
"CLOSE PagingCursor " +
"DEALLOCATE PagingCursor " +
"SELECT * FROM(" + sourceSql + ") AS T INNER JOIN @tblPK temp ON T." + key + " = temp.PK ";
return sql;
}