sql 分页存储过程
时间：2021-07-01 10:21:17
帮助过：3人阅读
USE [FcityDB２]
2 GO
3
4 /****** Object: StoredProcedure [dbo].[Proc_GetDataPaged] Script Date: 2015/7/24 10:15:05 ******/
5 SET ANSI_NULLS
ON
6 GO
7
8 SET QUOTED_IDENTIFIER
ON
9 GO
10
11
12
13
14 CREATE PROCEDURE [dbo].
[Proc_GetDataPaged]
15 @StrSelect VARCHAR(
max)
=NULL,
--欲显示的列（多列用逗号分开），例如：id,name
16 @StrFrom VARCHAR(
max)
= NULL,
--表名称，或者是表连接字符串，多表连接例如:student as s inner join dwinfo as dw on s.dwbh=dw.bh
17 @StrWhere VARCHAR(
max)
=NULL,
--查询条件,‘‘代表没有条件，单条件或者多条件，多条件例如：name=‘啊‘ and id=10
18 @StrOrder VARCHAR(
max)
=NULL,
--排序列(多个排序列用逗号分开），例如:id desc,name as
19 @ItemCount BIGINT output,
--总记录数
20 @PageSize INT =50,
--每页显示条数
21 @BeginIndex INT=1,
--记录开始数
22 @SqlQuery VARCHAR(
max) output
23 AS
24 BEGIN
25 SET NOCOUNT
ON;
26 DECLARE @sql NVARCHAR(
4000);
27 DECLARE @totalRecord INT;
28 --计算总记录数
29 IF (
@StrWhere = ‘‘‘‘
30 OR @StrWhere = ‘‘
31 OR @StrWhere IS NULL )
32 SET @sql = ‘select @totalRecord = count(*) from ‘
33 + @StrFrom
34 ELSE
35 SET @sql = ‘select @totalRecord = count(*) from ‘
36 + @StrFrom + ‘ where ‘ + @StrWhere
37
38 EXEC Sp_executesql
39 @sql,
40 N
‘@totalRecord int OUTPUT‘,
41 @ItemCount OUTPUT
--计算总记录数
42 -- DECLARE @SqlQuery VARCHAR(max)
43
44 IF(
@BeginIndex = 1
45 OR @BeginIndex = 0
46 OR @BeginIndex < 0 )
47 BEGIN
48 IF(
@StrWhere IS NULL )
--if(@StrWhere=‘‘)
49 SET @SqlQuery=‘select top ‘ + CONVERT(
VARCHAR,
@PageSize)
50 + ‘ row_number() over(order by ‘ + @StrOrder
51 + ‘ ) as RowNumber,‘ + @StrSelect + ‘ from ‘
52 + @StrFrom;
53 ELSE
54 SET @SqlQuery=‘select top ‘ + CONVERT(
VARCHAR,
@PageSize)
55 + ‘ row_number() over(order by ‘ + @StrOrder
56 + ‘ ) as RowNumber,‘ + @StrSelect + ‘ from ‘
57 + @StrFrom + ‘ where ‘ + @StrWhere;
58 --exec (@SqlQuery)
59 -- @SqlQuery
60 END
61 ELSE
62 BEGIN
63 IF(
@StrWhere IS NULL )
--if(@StrWhere=‘‘)
64 BEGIN
65 SET @SqlQuery=‘with cte as (
66
67 select row_number() over(order by ‘
68 + @StrOrder + ‘ ) as RowNumber,‘ + @StrSelect
69 + ‘ from ‘ + @StrFrom
70 + ‘
71
72 )
73 select * from cte where RowNumber between ‘
74 +
75 CONVERT(
VARCHAR, (
@BeginIndex-1)
*@PageSize+1)
76 + ‘ and ‘
77 + CONVERT(
VARCHAR,
@BeginIndex*@PageSize)
78 --print @SqlQuery
79 END
80 ELSE
81 BEGIN
82 SET @SqlQuery=‘with cte as (
83
84 select row_number() over(order by ‘
85 + @StrOrder + ‘ ) as RowNumber,‘ + @StrSelect
86 + ‘ from ‘ + @StrFrom + ‘ where ‘ + @StrWhere
87 + ‘
88
89 )
90
91 select * from cte where RowNumber between ‘
92 +
93
94 CONVERT(
VARCHAR, (
@BeginIndex-1)
*@PageSize+1)
95 + ‘ and ‘
96 + CONVERT(
VARCHAR,
@BeginIndex*@PageSize)
97 --print @SqlQuery
98 END
99 END
100
101
102 -- print @SqlQuery
103 -- EXEC (@SqlQuery)
104 -- select (@SqlQuery)
105 END
106
107
108
109
110 GO
sql 分页存储过程
标签：
