110,529
社区成员
发帖
与我相关
我的任务
分享
CREATE PROCEDURE [dbo].[pro_T_Common_CarryType_SelectPagedDynamic]
@PageSize int, --页面大小
@PageIndex int, --第几页
@TotalRowCount int OutPut, --总行数
@WhereCondition nvarchar(500) = NULL,
@OrderByExpression nvarchar(250) = NULL
AS
--SET NOCOUNT ON
--SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @SQL nvarchar(3250)
IF @PageIndex < 1
Begin
SET @PageIndex = 1
End
SET @SQL = 'select @TotalRowCount = count(*) from T_RateInfo'
IF @WhereCondition IS NOT NULL AND LEN(@WhereCondition) > 0
BEGIN
SET @SQL = @SQL + ' Where ' + @WhereCondition
End
EXEC sp_executesql @SQL,N'@TotalRowCount int OUTPUT',@TotalRowCount OUTPUT--计算总记录数
SET @SQL = 'with TempTBL as (SELECT ROW_NUMBER() OVER (ORDER BY '
IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
SET @SQL = @SQL + @OrderByExpression
END
Else
Begin
SET @SQL = @SQL + ' CarryType '
End
SET @SQL = @SQL + ' )AS Row, * from [dbo].[T_Common_CarryType]'
IF @WhereCondition IS NOT NULL AND LEN(@WhereCondition) > 0
BEGIN
SET @SQL = @SQL + ' Where ' + @WhereCondition
End
SET @SQL = @SQL +')
SELECT * FROM TempTBL where Row between ' + Ltrim(Rtrim(Str((@PageIndex-1)*@PageSize+1))) + ' and ' + Ltrim(Rtrim(Str((@PageIndex-1)*@PageSize+@PageSize)))
IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression
END
Else
Begin
SET @SQL = @SQL + ' ORDER BY CarryType '
End
EXEC sp_executesql @SQL