-------------------------------------
-----作者:張欣宇
-----時間:2013-06-28
-----簡介:根據參數和條件分頁查詢
-------------------------------------
Create proc [dbo].[Up_PagingQueryByParameter]
(
 ----- 表名或能查詢到結果的SQL語句{SQL語句左右必須有括號例:(select * from tbl1)}
 @TableName varchar(max),
 ----- 要查詢的列名語句; 可空默認*
 @ColumnName varchar(5000),
 ----- 用來排序的列; 不可為空
 @OrderByColumnName varchar(50),
 ----- 排序desc(倒序5.4.3.2.1)或asc(正序1.2.3.4.5); 可空默認asc
 @ShrtBy varchar(4),
 ----- Where條件; 可空默認1=1
 @Where varchar(5000),
 ----- 每頁顯示數; 可空默認20
 @PageShows int,
 ----- 當前頁數; 可空默認1
 @CurrentPage int,
 ----- 0為分頁查詢;其他為查詢全部; 可空默認0
 @IsPaging int
)
as
begin
 ----- 參數檢查及規范
 if isnull(@Where,N'')=N'' set @Where = N'1=1';
 if isnull(@ColumnName,N'')=N'' set @ColumnName = N'*';
 if isnull(@PageShows,0)1 set @PageShows = 20;
 if isnull(@CurrentPage,0)1 set @CurrentPage = 1;
 if isnull(@ShrtBy,N'')=N'' set @ShrtBy = 'asc';
 if isnull(@IsPaging,0)>1 set @IsPaging = 0;
 ----- 定義
 -- 拼接的SQL語句
 declare @SqlStr nvarchar(max);
 declare @SqlWithStr nvarchar(max);
 -- 開始條數
 declare @StartIndex int;
 -- 結束條數
 declare @EndIndex int;
 ----- 賦值
 set @StartIndex = (@CurrentPage-1)*@PageShows+1;
 print(@CurrentPage);
 print(@PageShows);
 print(@StartIndex);
 set @EndIndex = @CurrentPage*@PageShows;
 print(@EndIndex);
 set @OrderByColumnName=@OrderByColumnName+' '+@ShrtBy;
 ----- 分頁查詢
 set @SqlWithStr = N'with temp as(select ROW_NUMBER() over(order by '+@OrderByColumnName+N') as RowNumber,'+@ColumnName+N' from '+@TableName+N' as tableName where '+@Where+N')';
 if(@IsPaging = 0)
 begin
 set @SqlStr = @SqlWithStr + N' select '+@ColumnName+N' from temp where temp.RowNumber between '+convert(nvarchar(20),@StartIndex)+N' and '+convert(nvarchar(20),@EndIndex)+N'';
 ---- print(@SqlStr);
 exec(@SqlStr);
 ----- 總數查詢
 set @SqlStr = @SqlWithStr + N' select count(*) as TotalNumber from temp';
 ---- print(@SqlStr);
 exec(@SqlStr);
 end
 else
 begin
 set @SqlStr = @SqlWithStr + N' select '+@ColumnName+N' from temp';
 ---- print(@SqlStr);
 exec(@SqlStr);
 end
end