/**//****** Object: StoredProcedure [dbo].[Inovout_GetRecordFromPage] Script Date: 05/28/2007 15:12:11 ******/
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 &#64;SearchCondition <> &#39;&#39;
BEGIN
IF UPPER(SUBSTRING(&#64;SearchCondition,1,5)) <> &#39;WHERE&#39;
BEGIN
SET &#64;SearchCondition &#61; &#39;WHERE &#39; &#43; &#64;SearchCondition
END
END
PRINT &#64;SearchCondition
SET &#64;OrderExpression &#61; ISNULL(&#64;OrderExpression,&#39;&#39;)
SET &#64;OrderExpression &#61; LTRIM(RTRIM(&#64;OrderExpression))
IF &#64;OrderExpression <> &#39;&#39;
BEGIN
IF UPPER(SUBSTRING(&#64;OrderExpression,1,5)) <> &#39;WHERE&#39;
BEGIN
SET &#64;OrderExpression &#61; &#39;ORDER BY &#39; &#43; &#64;OrderExpression
END
END
PRINT &#64;OrderExpression
IF &#64;PageIndex IS NULL OR &#64;PageIndex < 1
BEGIN
SET &#64;PageIndex &#61; 1
END
PRINT &#64;PageIndex
IF &#64;PageSize IS NULL OR &#64;PageSize < 1
BEGIN
SET &#64;PageSize &#61; 10
END
PRINT &#64;PageSize
DECLARE &#64;SqlQuery VARCHAR(4000)
SET &#64;SqlQuery&#61;&#39;SELECT &#39;&#43;&#64;SelectList&#43;&#39;,RowNumber
FROM
(SELECT &#39; &#43; &#64;SelectList &#43; &#39;,ROW_NUMBER() OVER( &#39;&#43; &#64;OrderExpression &#43;&#39;) AS RowNumber
FROM &#39;&#43;&#64;TableSource&#43;&#39; &#39;&#43; &#64;SearchCondition &#43;&#39;) AS RowNumberTableSource
WHERE RowNumber BETWEEN &#39; &#43; CAST(((&#64;PageIndex - 1)* &#64;PageSize&#43;1) AS VARCHAR)
&#43; &#39; AND &#39; &#43;
CAST((&#64;PageIndex * &#64;PageSize) AS VARCHAR)
-- ORDER BY &#39; &#43; &#64;OrderExpression
PRINT &#64;SqlQuery
SET NOCOUNT ON
EXECUTE(&#64;SqlQuery)
SET NOCOUNT OFF
RETURN &#64;&#64;RowCount
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- &#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;
-- Author: Ristona
-- Create date: 2007-5-28
-- Description: 支持分页数据查询
-- &#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;&#61;
CREATE PROCEDURE [dbo].[GetRecordFromPage]
&#64;SelectList VARCHAR(2000), --欲选择字段列表
&#64;TableSource VARCHAR(100), --表名或视图表
&#64;SearchCondition VARCHAR(2000), --查询条件
&#64;OrderExpression VARCHAR(1000), --排序表达式
&#64;PageIndex INT &#61; 1, --页号,从0开始
&#64;PageSize INT &#61; 10 --页尺寸
AS
BEGIN
IF &#64;SelectList IS NULL OR LTRIM(RTRIM(&#64;SelectList)) &#61; &#39;&#39;
BEGIN
SET &#64;SelectList &#61; &#39;*&#39;
END
PRINT &#64;SelectList
SET &#64;SearchCondition &#61; ISNULL(&#64;SearchCondition,&#39;&#39;)
SET &#64;SearchCondition &#61; LTRIM(RTRIM(&#64;SearchCondition))
IF &#64;SearchCondition <> &#39;&#39;
BEGIN
IF UPPER(SUBSTRING(&#64;SearchCondition,1,5)) <> &#39;WHERE&#39;
BEGIN
SET &#64;SearchCondition &#61; &#39;WHERE &#39; &#43; &#64;SearchCondition
END
END
PRINT &#64;SearchCondition
SET &#64;OrderExpression &#61; ISNULL(&#64;OrderExpression,&#39;&#39;)
SET &#64;OrderExpression &#61; LTRIM(RTRIM(&#64;OrderExpression))
IF &#64;OrderExpression <> &#39;&#39;
BEGIN
IF UPPER(SUBSTRING(&#64;OrderExpression,1,5)) <> &#39;WHERE&#39;
BEGIN
SET &#64;OrderExpression &#61; &#39;ORDER BY &#39; &#43; &#64;OrderExpression
END
END
PRINT &#64;OrderExpression
IF &#64;PageIndex IS NULL OR &#64;PageIndex < 1
BEGIN
SET &#64;PageIndex &#61; 1
END
PRINT &#64;PageIndex
IF &#64;PageSize IS NULL OR &#64;PageSize < 1
BEGIN
SET &#64;PageSize &#61; 10
END
PRINT &#64;PageSize
DECLARE &#64;SqlQuery VARCHAR(4000)
SET &#64;SqlQuery&#61;&#39;SELECT &#39;&#43;&#64;SelectList&#43;&#39;,RowNumber
FROM
(SELECT &#39; &#43; &#64;SelectList &#43; &#39;,ROW_NUMBER() OVER( &#39;&#43; &#64;OrderExpression &#43;&#39;) AS RowNumber
FROM &#39;&#43;&#64;TableSource&#43;&#39; &#39;&#43; &#64;SearchCondition &#43;&#39;) AS RowNumberTableSource
WHERE RowNumber BETWEEN &#39; &#43; CAST(((&#64;PageIndex - 1)* &#64;PageSize&#43;1) AS VARCHAR)
&#43; &#39; AND &#39; &#43;
CAST((&#64;PageIndex * &#64;PageSize) AS VARCHAR)
-- ORDER BY &#39; &#43; &#64;OrderExpression
PRINT &#64;SqlQuery
SET NOCOUNT ON
EXECUTE(&#64;SqlQuery)
SET NOCOUNT OFF
RETURN &#64;&#64;RowCount
END
GO