新版本的SqlServer分布存储过程

SQL 2005及以上版本

/****** Object: StoredProcedure [dbo].[PageQuery] Script Date: 04/13/2012 14:26:57 ******/  
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,Name> -- Create date: <Create Date,> -- Description: <Description,> -- ============================================= CREATE PROCEDURE [dbo].[Page_Query] @TableName NVARCHAR(255),-- 表名 @Fields NVARCHAR(1000) = '*',-- 需要返回的列 @OrderFields NVARCHAR(255)='',-- 排序的字段名，不带ORDER BY 关键字，必填 @PageSize INT = 10,-- 页尺寸 @PageIndex INT = 1,-- 页码 @Where NVARCHAR(1500) = '',-- 查询条件 (注意: 不要加 where) @NeedCount BIT = 0,@RecordCount INT OUTPUT AS BEGIN DECLARE @ExceSql NVARCHAR(MAX) IF @Where !='' SET @Where =' WHERE ' + @Where SET @ExceSql='SELECT * FROM (SELECT ROW_NUMBER() OVER ( ORDER BY ' + @OrderFields + ') AS pos,' + @Fields + ' FROM ' + @TableName + @Where + ') AS sp WHERE pos BETWEEN '+STR( ( @PageIndex - 1 ) * @PageSize + 1 ) + ' AND ' + STR( @PageIndex * @PageSize ) EXEC (@ExceSql) SET @RecordCount = 0 IF @NeedCount = 1 BEGIN SET @ExceSql='SELECT @RecordCount=COUNT(1) FROM ' + @TableName + @Where EXEC sp_executesql @ExceSql,N'@RecordCount INT OUTPUT',@RecordCount OUTPUT END END 

SQL 2012及以上版本

/****** Object: StoredProcedure [dbo].[PageQuery] Script Date: 04/13/2012 14:26:57 ******/  
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,@RecordCount INT OUTPUT AS BEGIN DECLARE @ExceSql NVARCHAR(MAX) IF @Where !='' SET @Where =' WHERE ' + @Where SET @ExceSql='SELECT ' + @Fields + ' FROM ' + @TableName + @Where + ' ORDER BY ' + @OrderFields + ' OFFSET ' + STR( ( @PageIndex - 1 ) * @PageSize ) + ' ROWS FETCH NEXT ' + STR( @PageSize ) + ' ROWS ONLY' EXEC (@ExceSql) SET @RecordCount = 0 IF @NeedCount = 1 BEGIN SET @ExceSql='SELECT @RecordCount=COUNT(1) FROM ' + @TableName + @Where EXEC sp_executesql @ExceSql,@RecordCount OUTPUT END END 

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!