SQL Server 存储过程避免游标 [英] SQL Server stored procedure avoid cursor
本文介绍了SQL Server 存储过程避免游标的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下 SQL Server 存储过程:
I have the following SQL Server stored procedure :
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# 接口抛出超时异常.
It works successfully for small volume of data but When i try to execute it on a Large volume (i.e. more then 500,000 record) my C# interface throws the time out exception.
谁能帮我编辑我的存储过程以避免光标?
Can anyone help me edit my stored procedure to avoid the cursor?
感谢回复.
推荐答案
我通常使用以下内容:
SELECT @SampleOrderID = MIN (SampleOrderID) FROM #TempTable
WHILE @SampleOrderID IS NOT NULL
BEGIN
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
SELECT @SampleOrderID = MIN (SampleOrderID) FROM #TempTable WHERE SampleOrderID > @SampleOrderID
END
此代码将替换您拥有的光标内容.
This code would replace the cursor stuff you have.
这篇关于SQL Server 存储过程避免游标的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文