CREATE PROCEDURE [dbo].[RecordFromPage] @SelectList VARCHAR(max), @TableSource VARCHAR(100), @SearchCondition VARCHAR(max), @Ord ...
CREATE PROCEDURE [dbo].[RecordFromPage] @SelectList VARCHAR(max), @TableSource VARCHAR(100), @SearchCondition VARCHAR(max), @OrderExpression VARCHAR(1000), @PageIndex INT = 1, @PageSize INT = 10, @TotalCount int outputAS 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 @Total int DECLARE @Sql nVarchar(max) SET @Sql=(N'SELECT @Total=Count(*) FROM ' + @TableSource +' '+ @SearchCondition) print @sql Exec sp_executesql @Sql, N'@Total Int Out',@Total Out set @TotalCount=@Total DECLARE @SqlQuery VARCHAR(max) 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 END
原标题:C# 数据操作工具类
关键词:C#
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们:
admin#shaoqun.com
(#换成@)。