<script type="text/javascript">
</script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
T-SQLProgrammingPart2-BuildingaT- SQLLoop
ByGregoryA.Larsen
ThisisthesecondarticleinmyT-SQLprogrammingseries.ThisarticlewilldiscussbuildingaprogramloopusingT-SQL.Inadditiontotalkingaboutbuildingaloop,Iwillalsodiscusswaysofcontrollingtheloopprocessing,anddifferentmethodstobreakoutofaloop.
Aprogrammingloopisachunkofcodethatisexecutedoverandoveragain.Intheloopsomelogicisexecutedrepeatedlyinaniterativefashionuntilsomeconditionismetthatallowsthecodetobreakoutoftheloop.Oneexampleofwhereyoumightusealoopwouldbetoprocessthroughasetofrecordsonerecordatatime.Anotherexamplemightbewhereyouneedtogeneratesometestdataandaloopwouldallowyoutoinsertarecordintoyourtestdatatablewithslightlydifferentcolumnvalues,eachtimetheloopisexecuted.InthisarticleIwilldiscusstheWHILE,BREAK,CONTINUE,andGOTOstatements.
WHILEStatement
InT-SQLtheWHILEstatementisthemostcommonlyusedwaytoexecutealoop.HereisthebasicsyntaxforaWHILEloop:
WHILE<Booleanexpression><codeblock>
Wherea<Booleanexpression>isanyexpressionthatequatestoatrueorfalseanswer,andthe<codeblock>isthedesirecodetobeexecutedwhilethe<Booleanexpression>istrue.Let'sgothrougharealsimpleexample.
InthisexampleIwillincrementacounterfrom1to10anddisplaythecountereachtimethroughtheWHILEloop.
declare@counterintset@counter=0while@counter<10beginset@counter=@counter+1print'Thecounteris'+cast(@counteraschar)end
HerethecodeexecutestheWHILEstatementaslongasthe@counterintegervariableislessthan10,thisistheBooleanexpressionoftheWHILEloop.The@countervariablestartsoutatzero,andeachtimethroughtheWHILEloopitisincrementedby1.ThePRINTstatementdisplaysthevalueinthe@countervariableeachtimethroughtheWHILEloop.Theoutputfromthissamplelookslikethis:
Thecounteris1Thecounteris2Thecounteris3Thecounteris4Thecounteris5Thecounteris6Thecounteris7Thecounteris8Thecounteris9Thecounteris10
Asyoucansee,oncethe@countervariablereaches10theBooleanexpressionthatiscontrollingtheWHILEloopisnolongertrue,sothecodewithinthewhileloopisnolongerexecuted.
Notonlycanyouhaveasinglewhileloop,butyoucanhaveWHILEloopsinsideWHILEloops.OrcommonlyknowasnestingofWHILEloops.Therearelotsofdifferentuseswherenestingisvaluable.IcommonlyusenestingofWHILEloopstogeneratetestdata.MynextexamplewillusetheWHILElooptogeneratetestrecordsforaPARTtable.AgivenPARTrecordisuniquelyidentifiedbyaPart_Id,andaCategory_Id.ForeachPart_IdtherearethreedifferentCategory_Id's.Hereismyexamplethatgenerates6uniquerecordsformyPARTtableusinganestedWHILEloop.
declare@Part_Idintdeclare@Category_Idintdeclare@Descvarchar(50)createtablePART(Part_Idint,Category_Idint,Descriptionvarchar(50))set@Part_Id=0set@Category_Id=0while@Part_Id<2beginset@Part_Id=@Part_Id+1while@Category_Id<3beginset@Category_Id=@Category_Id+1set@Desc='Part_Idis'+cast(@Part_Idaschar(1))+'Category_Id'+cast(@Category_Idaschar(1))insertintoPARTvalues(@Part_Id,@Category_Id,@Desc)endset@Category_Id=0endselect*fromPARTdroptablePART共4页 1
<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript">
</script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
November5,2003
T-SQLProgrammingPart2-BuildingaT- SQLLoop
ByGregoryA.Larsen
ThisisthesecondarticleinmyT-SQLprogrammingseries.ThisarticlewilldiscussbuildingaprogramloopusingT-SQL.Inadditiontotalkingaboutbuildingaloop,Iwillalsodiscusswaysofcontrollingtheloopprocessing,anddifferentmethodstobreakoutofaloop.
Aprogrammingloopisachunkofcodethatisexecutedoverandoveragain.Intheloopsomelogicisexecutedrepeatedlyinaniterativefashionuntilsomeconditionismetthatallowsthecodetobreakoutoftheloop.Oneexampleofwhereyoumightusealoopwouldbetoprocessthroughasetofrecordsonerecordatatime.Anotherexamplemightbewhereyouneedtogeneratesometestdataandaloopwouldallowyoutoinsertarecordintoyourtestdatatablewithslightlydifferentcolumnvalues,eachtimetheloopisexecuted.InthisarticleIwilldiscusstheWHILE,BREAK,CONTINUE,andGOTOstatements.
WHILEStatement
InT-SQLtheWHILEstatementisthemostcommonlyusedwaytoexecutealoop.HereisthebasicsyntaxforaWHILEloop:
WHILE<Booleanexpression><codeblock>
Wherea<Booleanexpression>isanyexpressionthatequatestoatrueorfalseanswer,andthe<codeblock>isthedesirecodetobeexecutedwhilethe<Booleanexpression>istrue.Let'sgothrougharealsimpleexample.
InthisexampleIwillincrementacounterfrom1to10anddisplaythecountereachtimethroughtheWHILEloop.
declare@counterintset@counter=0while@counter<10beginset@counter=@counter+1print'Thecounteris'+cast(@counteraschar)end
HerethecodeexecutestheWHILEstatementaslongasthe@counterintegervariableislessthan10,thisistheBooleanexpressionoftheWHILEloop.The@countervariablestartsoutatzero,andeachtimethroughtheWHILEloopitisincrementedby1.ThePRINTstatementdisplaysthevalueinthe@countervariableeachtimethroughtheWHILEloop.Theoutputfromthissamplelookslikethis:
Thecounteris1Thecounteris2Thecounteris3Thecounteris4Thecounteris5Thecounteris6Thecounteris7Thecounteris8Thecounteris9Thecounteris10
Asyoucansee,oncethe@countervariablereaches10theBooleanexpressionthatiscontrollingtheWHILEloopisnolongertrue,sothecodewithinthewhileloopisnolongerexecuted.
Notonlycanyouhaveasinglewhileloop,butyoucanhaveWHILEloopsinsideWHILEloops.OrcommonlyknowasnestingofWHILEloops.Therearelotsofdifferentuseswherenestingisvaluable.IcommonlyusenestingofWHILEloopstogeneratetestdata.MynextexamplewillusetheWHILElooptogeneratetestrecordsforaPARTtable.AgivenPARTrecordisuniquelyidentifiedbyaPart_Id,andaCategory_Id.ForeachPart_IdtherearethreedifferentCategory_Id's.Hereismyexamplethatgenerates6uniquerecordsformyPARTtableusinganestedWHILEloop.
declare@Part_Idintdeclare@Category_Idintdeclare@Descvarchar(50)createtablePART(Part_Idint,Category_Idint,Descriptionvarchar(50))set@Part_Id=0set@Category_Id=0while@Part_Id<2beginset@Part_Id=@Part_Id+1while@Category_Id<3beginset@Category_Id=@Category_Id+1set@Desc='Part_Idis'+cast(@Part_Idaschar(1))+'Category_Id'+cast(@Category_Idaschar(1))insertintoPARTvalues(@Part_Id,@Category_Id,@Desc)endset@Category_Id=0endselect*fromPARTdroptablePART共4页 1
<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript">
</script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>