發(fā)表日期:2015-10-13 文章編輯:南昌開優(yōu)網(wǎng)絡(luò) 瀏覽次數(shù):4425 標(biāo)簽:SQL使用
01.
/****** Object: StoredProcedure [dbo].[proc_SplitPage] ******/
02.
SET ANSI_NULLS ON
03.
GO
04.
SET QUOTED_IDENTIFIER ON
05.
GO
06.
CREATE PROCEDURE [dbo].[proc_SplitPage]
07.
08.
@tblName varchar(255), -- 表名
09.
@strFields varchar(255), -- 顯示字段名
10.
@strOrder varchar(255), -- 排序字段名
11.
@strOrderType varchar(50), -- 設(shè)置排序類型, asc || desc
12.
@PageSize int = 10, -- 頁(yè)尺寸
13.
@PageIndex int = 1, -- 頁(yè)碼
14.
@strWhere varchar(1000) =
''
-- 查詢條件 (注意: 不要加 where)
15.
AS
16.
declare @strSQL varchar(6000) -- 主語(yǔ)句
17.
declare @strTmp varchar(100) -- 臨時(shí)變量
18.
declare @strOrderTemp varchar(400) -- 排序類型
19.
if
@strOrderType =
'desc'
20.
begin
21.
set @strTmp =
'<(select min'
22.
set @strOrderTemp =
' order by ['
+ @strOrder +
'] desc'
23.
end
24.
else
25.
begin
26.
set @strTmp =
'>(select max'
27.
set @strOrderTemp =
' order by ['
+ @strOrder +
'] asc'
28.
end
29.
set @strSQL =
'select top '
+ str(@PageSize) +
' '
+ @strFields +
' from ['
30.
+ @tblName +
'] where ['
+ @strOrder +
']'
+ @strTmp +
'(['
31.
+ @strOrder +
']) from (select top '
+ str((@PageIndex-1)*@PageSize) +
' ['
32.
+ @strOrder +
'] from ['
+ @tblName +
']'
+ @strOrderTemp +
') as tblTmp)'
33.
+ @strOrderTemp
34.
if
@strWhere !=
''
35.
set @strSQL =
'select top '
+ str(@PageSize) +
' '
+ @strFields +
' from ['
36.
+ @tblName +
'] where ['
+ @strOrder +
']'
+ @strTmp +
'(['
37.
+ @strOrder +
']) from (select top '
+ str((@PageIndex-1)*@PageSize) +
' ['
38.
+ @strOrder +
'] from ['
+ @tblName +
'] where '
+ @strWhere +
' '
39.
+ @strOrderTemp +
') as tblTmp) and '
+ @strWhere +
' '
+ @strOrderTemp
40.
if
@PageIndex = 1
41.
begin
42.
set @strTmp =
''
43.
if
@strWhere !=
''
44.
set @strTmp =
' where '
+ @strWhere
45.
set @strSQL =
'select top '
+ str(@PageSize) +
' '
+ @strFields +
' from ['
46.
+ @tblName +
']'
+ @strTmp +
' '
+ @strOrderTemp
47.
end
48.
exec (@strSQL)
49.
GO