winform 分页mssql存储过程

发布时间：2016/7/24 12:28:18  次浏览  作者:admin

winform 分页mssql存储过程

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='NetWingPager')
DROP PROC NetWingPager
GO
CREATE PROCEDURE [dbo].[NetWingPager]
	@Table VARCHAR(1000), --表名,多表是请使用 tA a inner join tB b On a.AID = b.AID
	@TIndex NVARCHAR(100),    --主键，可以带表头 a.AID
	@Column NVARCHAR(2000) = '*',--读取字段
	@Sql NVARCHAR(3000) = '',--Where条件
	@PageIndex INT = 1,    --开始页码
	@PageSize INT = 10,        --页大小
	@Sort NVARCHAR(200) = '' --排序字段

AS

--_PageTest 'HoursMIPK','id','*','',1,10,'id'

DECLARE @strWhere VARCHAR(2000)
DECLARE @strsql NVARCHAR(3900)
IF @Sql IS NOT NULL AND len(LTRIM(RTRIM(@Sql)))>0
  BEGIN
   SET @strWhere = ' WHERE ' + @Sql + ' '
  END
ELSE
  BEGIN
   SET @strWhere = ''
  END
        
IF (charindex(LTRIM(RTRIM(@TIndex)),@Sort)=0)
BEGIN
	IF(@Sort='')
		SET @Sort = @TIndex + ' DESC '
	ELSE
		SET @Sort = @Sort+ ' , '+@TIndex + ' DESC '
END
IF @PageIndex < 1
  SET @PageIndex = 1

		IF @PageIndex = 1 --第一页提高性能
		BEGIN 
		  SET @strsql = 'SELECT TOP ' + str(@PageSize) +' '+@Column+ '  FROM ' + @Table + ' ' + @strWhere + ' ORDER BY  '+ @Sort
		END 
		ELSE
		  BEGIN
		 
			DECLARE @START_ID NVARCHAR(50)
			DECLARE @END_ID NVARCHAR(50)
			SET @START_ID = convert(NVARCHAR(50),(@PageIndex - 1) * @PageSize + 1)
			SET @END_ID = convert(NVARCHAR(50),@PageIndex * @PageSize)
			SET @strsql =  ' SELECT '+@Column+ '
		   FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS RowNum, 
			 '+@Column+ '
			  FROM '+@Table +' WITH(NOLOCK) ' + @strWhere +') AS D
		   WHERE RowNum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY '+@Sort
		  END
EXEC(@strsql)
PRINT @strsql
	SET @strsql = 'SELECT  Count(1) as TotalRecords FROM ' + @Table +' WITH(NOLOCK) ' + @strWhere  
PRINT @strsql
EXEC(@strsql)