SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
=============================================
--
Author: Ristona
--
Create date:
2007
-
5
-
28
--
Description: 支持分页数据查询
--
=============================================
CREATE PROCEDURE [dbo].[GetRecordFromPage]
@SelectList VARCHAR(
2000
),
--
欲选择字段列表
@TableSource VARCHAR(
100
),
--
表名或视图表
@SearchCondition VARCHAR(
2000
),
--
查询条件
@OrderExpression VARCHAR(
1000
),
--
排序表达式
@PageIndex INT
=
1
,
--
页号,从0开始
@PageSize INT
=
10
--
页尺寸
AS
BEGIN
IF @SelectList IS NULL OR LTRIM(RTRIM(@SelectList))
=
''
BEGIN
SET @SelectList
=
'
*
'
END
PRINT @SelectList
SET @SearchCondition
=
ISNULL(@SearchCondition,
''
)
SET @SearchCondition
=
LTRIM(RTRIM(@SearchCondition))
IF @SearchCondition
<>
''
BEGIN
IF UPPER(SUBSTRING(@SearchCondition,
1
,
5
))
<>
'
WHERE
'
BEGIN
SET @SearchCondition
=
'
WHERE
'
+
@SearchCondition
END
END
PRINT @SearchCondition
SET @OrderExpression
=
ISNULL(@OrderExpression,
''
)
SET @OrderExpression
=
LTRIM(RTRIM(@OrderExpression))
IF @OrderExpression
<>
''
BEGIN
IF UPPER(SUBSTRING(@OrderExpression,
1
,
5
))
<>
'
WHERE
'
BEGIN
SET @OrderExpression
=
'
ORDER BY
'
+
@OrderExpression
END
END
PRINT @OrderExpression
IF @PageIndex IS NULL OR @PageIndex
<
1
BEGIN
SET @PageIndex
=
1
END
PRINT @PageIndex
IF @PageSize IS NULL OR @PageSize
<
1
BEGIN
SET @PageSize
=
10
END
PRINT @PageSize
DECLARE @SqlQuery VARCHAR(
4000
)
SET @SqlQuery
=
'
SELECT
'
+
@SelectList
+
'
,RowNumber
FROM
(SELECT
'
+ @SelectList +
'
,ROW_NUMBER() OVER(
'
+ @OrderExpression +
'
) AS RowNumber
FROM
'
+@TableSource+
'
'
+ @SearchCondition +
'
) AS RowNumberTableSource
WHERE RowNumber BETWEEN
'
+ CAST(((@PageIndex - 1)* @PageSize+1) AS VARCHAR)
+
'
AND
'
+
CAST((@PageIndex
*
@PageSize) AS VARCHAR)
--
ORDER BY
'
+ @OrderExpression
PRINT @SqlQuery
SET NOCOUNT ON
EXECUTE(@SqlQuery)
SET NOCOUNT OFF
RETURN @@RowCount
END
GO
GO
SET QUOTED_IDENTIFIER ON
GO
--
=============================================
--
Author: Ristona
--
Create date:
2007
-
5
-
28
--
Description: 支持分页数据查询
--
=============================================
CREATE PROCEDURE [dbo].[GetRecordFromPage] @SelectList VARCHAR(
2000
),
--
欲选择字段列表 @TableSource VARCHAR(
100
),
--
表名或视图表 @SearchCondition VARCHAR(
2000
),
--
查询条件 @OrderExpression VARCHAR(
1000
),
--
排序表达式 @PageIndex INT
=
1
,
--
页号,从0开始 @PageSize INT
=
10
--
页尺寸AS BEGIN IF @SelectList IS NULL OR LTRIM(RTRIM(@SelectList))
=
''
BEGIN SET @SelectList
=
'
*
'
END PRINT @SelectList SET @SearchCondition
=
ISNULL(@SearchCondition,
''
) SET @SearchCondition
=
LTRIM(RTRIM(@SearchCondition)) IF @SearchCondition
<>
''
BEGIN IF UPPER(SUBSTRING(@SearchCondition,
1
,
5
))
<>
'
WHERE
'
BEGIN SET @SearchCondition
=
'
WHERE
'
+
@SearchCondition END END PRINT @SearchCondition SET @OrderExpression
=
ISNULL(@OrderExpression,
''
) SET @OrderExpression
=
LTRIM(RTRIM(@OrderExpression)) IF @OrderExpression
<>
''
BEGIN IF UPPER(SUBSTRING(@OrderExpression,
1
,
5
))
<>
'
WHERE
'
BEGIN SET @OrderExpression
=
'
ORDER BY
'
+
@OrderExpression END END PRINT @OrderExpression IF @PageIndex IS NULL OR @PageIndex
<
1
BEGIN SET @PageIndex
=
1
END PRINT @PageIndex IF @PageSize IS NULL OR @PageSize
<
1
BEGIN SET @PageSize
=
10
END PRINT @PageSize DECLARE @SqlQuery VARCHAR(
4000
) SET @SqlQuery
=
'
SELECT
'
+
@SelectList
+
'
,RowNumber
FROM (SELECT
'
+ @SelectList +
'
,ROW_NUMBER() OVER(
'
+ @OrderExpression +
'
) AS RowNumber FROM
'
+@TableSource+
'
'
+ @SearchCondition +
'
) AS RowNumberTableSource WHERE RowNumber BETWEEN
'
+ CAST(((@PageIndex - 1)* @PageSize+1) AS VARCHAR)
+
'
AND
'
+
CAST((@PageIndex
*
@PageSize) AS VARCHAR)
--
ORDER BY
'
+ @OrderExpression
PRINT @SqlQuery SET NOCOUNT ON EXECUTE(@SqlQuery) SET NOCOUNT OFF RETURN @@RowCountENDGO
扫一扫
173
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
