我把整个存储过程贴出来吧。。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetRecordFromPage]
@SelectList VARCHAR(2000),
@TableSource VARCHAR(500),
@SearchCondition VARCHAR(max),
@OrderExpression VARCHAR(1000),
@pagetab VARCHAR(100),
@PageIndex INT = 1,
@PageSize INT = 10
AS
Begin
IF @SelectList IS NULL OR LTRIM(RTRIM(@SelectList)) = ''
Begin
SET @SelectList = '*'
End
SET @SelectList=Replace(@SelectList,'rownumber,','')
PRINT @SelectList
SET @SearchCondition = ISNULL(@SearchCondition,'')
IF @SearchCondition <> ''
Begin
IF UPPER(SUBSTRING(@SearchCondition,1,5)) <> 'WHERE'
Begin
SET @SearchCondition = 'WHERE 1=1 ' + @SearchCondition
SET @SearchCondition = Replace(@SearchCondition,'and and','and')
SET @SearchCondition = Replace(@SearchCondition,'and and','and')
SET @SearchCondition = Replace(@SearchCondition,'and and','and')
End
End
PRINT @SearchCondition
SET @OrderExpression = ISNULL(@OrderExpression,'')
SET @OrderExpression = LTRIM(RTRIM(@OrderExpression))
IF @OrderExpression <> ''
Begin
IF UPPER(SUBSTRING(@OrderExpression,1,5)) <> 'ORDER BY'
Begin
SET @OrderExpression = 'ORDER BY ' + @OrderExpression
End
End
PRINT @OrderExpression
SET @pagetab = ISNULL(@pagetab,'')
SET @pagetab = LTRIM(RTRIM(@pagetab))
IF @pagetab <> ''
Begin
IF UPPER(SUBSTRING(@pagetab,1,6)) <> '##page'
Begin
SET @pagetab = '##page' + @pagetab
End
End
PRINT @pagetab
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 *
into '+@pagetab+' 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)
PRINT @SqlQuery
SET NOCOUNT ON
EXECUTE(@SqlQuery)
SET NOCOUNT off
End