SQL Server 存储过程 返回结果集问题
存储过程如下
CREATE PROCEDURE [dbo].[P_GetRecordInformationCountData]
@startIndex int,
@endIndex int,
@pageSize int,
@strWhere nvarchar(500)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

  DECLARE @LowerBand int,@UpperBand int,@sql nvarchar(1000)
  set @LowerBand=(@startIndex-1)*@pageSize
  set @UpperBand=@endIndex*@pageSize+1
  select *,ROW_NUMBER() over (order by SystemDebug_RecordInformation_ID) as RowNumber into #tempRecordInformationData from SystemDebug_RecordInformation
  if @strWhere!=''
  set @sql=N'select * from #tempRecordInformationData where RowNumber>'+convert(nvarchar,@LowerBand)+N' and RowNumber<'+convert(nvarchar,@UpperBand)+N' and '+@strWhere 
  else
  set @sql=N'select * from #tempRecordInformationData where RowNumber>'+convert(nvarchar,@LowerBand)+N' and RowNumber<'+convert(nvarchar,@UpperBand)
  exec(@sql)
END


上述存储过程若在SQLServer 管理器中，执行则能够返回指定的结果集(最后exec的结果集),但是在C#中调用时却返回的是第一个select 的结果集。
希望各位大虾能说下这是什么情况吗？有没处理办法呢？

------解决方案--------------------
把存储过程改一下，不用插入临时表，如下

SQL code

SET @LowerBand = (@startIndex -1) * @pageSize
SET @UpperBand = @endIndex * @pageSize + 1
;WITH cte AS(
SELECT *,
       RowNumber=ROW_NUMBER() OVER(ORDER BY SystemDebug_RecordInformation_ID)
FROM   SystemDebug_RecordInformation
)

IF @strWhere != ''
    SET @sql = N'select * from cte where RowNumber>' + 
        CONVERT(NVARCHAR, @LowerBand) + N' and RowNumber<' + CONVERT(NVARCHAR, @UpperBand)
        + N' and ' + @strWhere
ELSE
    SET @sql = N'select * from cte where RowNumber>' + 
        CONVERT(NVARCHAR, @LowerBand) + N' and RowNumber<' + CONVERT(NVARCHAR, @UpperBand)