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) * @pageSizeSET @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 ' + @strWhereELSE    SET @sql = N'select * from cte where RowNumber>' +         CONVERT(NVARCHAR, @LowerBand) + N' and RowNumber<' + CONVERT(NVARCHAR, @UpperBand)