话不多说,请看代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
-------------------------------------
-----作者:张欣宇
-----时间: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
|
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,同时也希望多多支持服务器之家!
原文链接:http://www.cnblogs.com/zhang625161495/p/6217020.html