1 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
