ProcessingSequentiallyThroughaSetofRecords

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

November19,2003
T-SQLProgrammingPart3-ProcessingSequentiallyThroughaSetofRecords
ByGregoryA.Larsen


Atsomepointyouwillhavesomebusinesslogicthatwillrequireyoutoprocesssequentiallythroughasetofrecordsonerecordatatime.Forexampleyoumayhavealistofdatabases,andforeachdatabaseyoumaywanttobuildacommandthatwillperformsomeprocessagainsteachdatabase.Oryoumighthaveasetofrecordswhereyouwanttoprocessthrougheachrecordoneatatime,soyoucanselectadditionalinformationfromanothertablebasedontheinformationcontainedineachrecord.Thisarticlewilldiscusstwodifferentwaystoprocessthroughasetofrecordsonerecordatatime.
UsingaCursor
ThefirstmethodIwilldiscussusesacursortoprocessthroughasetofrecordsonerecordatatime.Acursorisbasicallyasetofrowsthatyoudefinebasedonarecordsetreturnedfromaquery.Acursorallowsapplicationsamechanismtoprocessthrougharesultsetonerowatatime.Withacursoranapplicationisallowedtopositionitselftoaspecificrow,scrollbackandforth,andanumberofotherthings.Itwouldtakeaseriesofarticlestodescribeallthefunctionalityofacursor.ForthepurposeofthisarticleI'monlygoingtofocusonhowtousethedefaultscrollingfunctionalityofacursor.Thisdefaultfunctionalitywillonlyreadfromthefirstrowtothelastrowinacursor,onerowatatime.Iwillleaveadditionalcursortopicstoanotherarticleseries.

TodefineacursortheDECLARECURSORstatementisused.HereisthebasicformatforthesimplecursortopicIwillbediscussinginthisarticle.

DECLAREcursor_nameCURSORFORselect_statement

Thecursor_nameisthenameyouwanttoassociatewiththecursor.Theselect_statementisthequerythatwilldeterminetherowsthatmakeupthecursor.Notethereareotherparameters/optionsassociatedwiththeDECLARECURSORstatementthathelpdefinemorecomplicatedcursorprocessingthanIwillbecoveringinthisarticle.FortheseadditionaloptionspleasereadMicrosoftBooksOnline.

Let'sreviewafairlysimplecursorexample.Thisexamplewilldefineacursorthatcontainsthetop5Customer_Id'sintheCustomertableintheNorthwinddatabase.ItwillthenprocessthrougheachrecorddisplayingarownumberandtheCustomerIDforeach.Hereisthecodetodothis.

declare@CustIdnchar(5)declare@RowNumintdeclareCustListcursorforselecttop5CustomerIDfromNorthwind.dbo.CustomersOPENCustListFETCHNEXTFROMCustListINTO@CustIdset@RowNum=0WHILE@@FETCH_STATUS=0BEGINset@RowNum=@RowNum+1printcast(@RowNumaschar(1))+''+@CustIdFETCHNEXTFROMCustListINTO@CustIdENDCLOSECustListDEALLOCATECustList

HerearetheresultsthataregeneratedfromtheprintstatementwhenIrunitagainstmyNorthwindDatabase.

1ALFKI2ANATR3ANTON4AROUT5BERGS

Let'slookattheabovecodeinalittlemoredetail.Ifirstdeclaredacursorcalled"CustList".The"CustList"cursorispopulatedusingaSELECTstatementthatusestheTOPclausetoreturnonlythetop5CustomerId's.Nextthecursorisopened.Eachrecordinthe"CustList"cursorisretrieved,onerecordatatime,usingthe"FETCHNEXT"nextstatement.The"FETCHNEXT"[e-mail]@@FETCH_STATUSvariablecontrolswhethertheWHILEloopisexecuted.@@FETCH_STATUSissettozerowhenarecordissuccessfullyretrievedfromthecursor"CustList"[e-mail][e-mail],a"FETCHNEXT"statementisusedtoretrievethenextrowbeforethenextcycleoftheWHILEloop.Thisprocesscontinuesonerecordatatimeuntilallrecordsincursor"CustList"havebeenprocessed.1<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>