存储过程如下
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)[email protected]
set @[email protected][email protected]+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 [email protected]
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)