我有以下sql Server存储过程：
BEGIN TRAN
CREATE TABLE #TempTable (
SampleOrderID int,SampleOrderNo varchar(512),ChallanNoAndChallanDate varchar(MAX)
)
CREATE NONCLUSTERED INDEX #IX_Temp2_1 ON #TempTable(SampleOrderID)
DECLARE
@sql as varchar(MAX)
SET @sql=' SELECT SampleOrderID,SampleOrderNo FROM SampleOrder WHERE SampleOrderID IN (37808,37805,37767,37571,37745,37772,37843,37394,37909,37905,37903) '
INSERT INTO #TempTable (SampleOrderID,SampleOrderNo)
EXEC (@sql)
DECLARE
@SampleOrderID as int,@ChallanNoAndChallanDate as varchar(max)
DECLARE Cur_AB1 CURSOR GLOBAL FORWARD_ONLY KEYSET FOR
SELECT SampleOrderID FROM #TempTable
OPEN Cur_AB1
FETCH NEXT FROM Cur_AB1 INTO @SampleOrderID
WHILE(@@Fetch_Status <> -1)
BEGIN--2
SET @ChallanNoAndChallanDate=''
SELECT @ChallanNoAndChallanDate= COALESCE(@ChallanNoAndChallanDate+ ',','') + CONVERT(VARCHAR(12),ChallanDate,106)+':'+ChallanNo FROM Challan WHERE OrderID =@SampleOrderID AND OrderType=2
UPDATE #TempTable SET ChallanNoAndChallanDate=@ChallanNoAndChallanDate WHERE SampleOrderID=@SampleOrderID
FETCH NEXT FROM Cur_AB1 INTO @SampleOrderID
END--2
CLOSE Cur_AB1
DEALLOCATE Cur_AB1
SELECT * FROM #TempTable
DROP TABLE #TempTable
COMMIT TRAN
输出：
SamID SamNo ChallanNoAndDaet 37394 37394,31 May 2012:151592 37571 37571,31 May 2012:151580 37745 37745,31 May 2012:151582 37767 37767,30 May 2012:151507,31 May 2012:151576 37772 37772,31 May 2012:151587 37805 37805,31 May 2012:151574 37808 37808,31 May 2012:151573 37843 37843,31 May 2012:151588 37903 37903,31 May 2012:151597 37905 37905,31 May 2012:151596 37909 37909,31 May 2012:151593
它适用于少量数据,但当我尝试在大卷(即超过500,000条记录)上执行它时,我的C#接口会抛出超时异常.
任何人都可以帮我编辑我的存储过程以避免光标？
谢谢你的回复.
解决方法
我通常使用以下内容：
SELECT @SampleOrderID = MIN (SampleOrderID) FROM #TempTable WHILE @SampleOrderID IS NOT NULL BEGIN SET @ChallanNoAndChallanDate='' SELECT @ChallanNoAndChallanDate= COALESCE(@ChallanNoAndChallanDate+ ',106)+':'+ChallanNo FROM Challan WHERE OrderID =@SampleOrderID AND OrderType=2 UPDATE #TempTable SET ChallanNoAndChallanDate=@ChallanNoAndChallanDate WHERE SampleOrderID=@SampleOrderID SELECT @SampleOrderID = MIN (SampleOrderID) FROM #TempTable WHERE SampleOrderID > @SampleOrderID END
此代码将替换您拥有的光标.
