-------------------------------------
-----作者:張欣宇
-----時間:2013-06-28
-----簡介:根據(jù)參數(shù)和條件分頁查詢
-------------------------------------
Create proc [dbo].[Up_PagingQueryByParameter]
(
----- 表名或能查詢到結(jié)果的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),
----- 每頁顯示數(shù); 可空默認20
@PageShows int,
----- 當前頁數(shù); 可空默認1
@CurrentPage int,
----- 0為分頁查詢;其他為查詢?nèi)? 可空默認0
@IsPaging int
)
as
begin
----- 參數(shù)檢查及規(guī)范
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);
-- 開始條數(shù)
declare @StartIndex int;
-- 結(jié)束條數(shù)
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);
----- 總數(shù)查詢
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
