C# SQL 分页

DECLARE 	@TableName VARCHAR(100)='tb',  --表名
	@StrWhere NVARCHAR(2000)='',   --条件
	@OrderByColumnName VARCHAR(100)='ID',--排序条件
	@PageIndex INT=0,--初始页
	@PageSize INT=2--页大小

DECLARE @SqlStr NVARCHAR(4000)
		DECLARE @SqlCount NVARCHAR(4000)
		DECLARE @StartIndex VARCHAR(10),@EndIndex VARCHAR(10)
		SET @StartIndex=CONVERT(VARCHAR(10),@PageIndex*@PageSize+1)
		SET @EndIndex=CONVERT(VARCHAR(10),(@PageIndex+1)*@PageSize)

		SET @SqlStr= 'SELECT * FROM ('
		SET	@SqlStr+='SELECT ROW_NUMBER()OVER(ORDER BY '+@OrderByColumnName+') AS RowIndex, * '
		SET	@SqlStr+='FROM '+@TableName+' '
		SET	@SqlStr+=CASE WHEN LEN(@StrWhere)>0 THEN 'WHERE '+@StrWhere+' ) T ' ELSE ') T ' END
		SET	@SqlStr+='WHERE  T.RowIndex BETWEEN '+@StartIndex+' AND '+@EndIndex+' '
		SET @SqlStr+=CASE WHEN LEN(@OrderByColumnName)>0 THEN 'ORDER BY '+ @OrderByColumnName + '' ELSE '' END

		SET @SqlCount='SELECT COUNT(1) FROM '+@TableName
		SET @SqlCount+=CASE WHEN LEN(@StrWhere)>0 THEN ' WHERE '+@StrWhere+' ' ELSE ' ' END

		--SELECT @SqlStr
		--SELECT @SqlCount

		EXEC(@SqlCount)
		EXEC(@SqlStr)