effectssqlserver2012

SQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8Chapter8InThisChaptercProceduralExtensionscStoredProcedurescUser-DefinedFunctionsStoredProceduresandUser-DefinedFunctionsCh08.
indd2271/25/129:39:24AM228MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8Thischapterintroducesbatchesandroutines.
AbatchisasequenceofTransact-SQLstatementsandproceduralextensions.
Aroutinecanbeeitherastoredprocedureorauser-definedfunction(UDF).
ThebeginningofthechapterintroducesallproceduralextensionssupportedbytheDatabaseEngine.
Afterthat,proceduralextensionsareused,togetherwithTransact-SQLstatements,toshowhowbatchescanbeimplemented.
Abatchcanbestoredasadatabaseobject,aseitherastoredprocedureoraUDF.
Somestoredproceduresarewrittenbyusers,andothersareprovidedbyMicrosoftandarereferredtoassystemstoredprocedures.
Incontrasttouser-definedstoredprocedures,UDFsreturnavaluetoacaller.
AllroutinescanbewritteneitherinTransact-SQLorinanotherprogramminglanguagesuchasC#orVisualBasic.
Theendofthechapterintroducestable-valuedparameters.
ProceduralExtensionsTheprecedingchaptersintroducedTransact-SQLstatementsthatbelongtothedatadefinitionlanguageandthedatamanipulationlanguage.
Mostofthesestatementscanbegroupedtogethertobuildabatch.
Aspreviouslymentioned,abatchisasequenceofTransact-SQLstatementsandproceduralextensionsthataresenttothedatabasesystemforexecutiontogether.
Thenumberofstatementsinabatchislimitedbythesizeofthecompiledbatchobject.
Themainadvantageofabatchoveragroupofsingletonstatementsisthatexecutingallstatementsatoncebringssignificantperformancebenefits.
ThereareanumberofrestrictionsconcerningtheappearanceofdifferentTransact-SQLstatementsinsideabatch.
ThemostimportantisthatthedatadefinitionstatementsCREATEVIEW,CREATEPROCEDURE,andCREATETRIGGERmusteachbetheonlystatementinabatch.
NoteToseparateDDLstatementsfromoneanother,usetheGOstatement.
ThefollowingsectionsdescribeeachproceduralextensionoftheTransact-SQLlanguageseparately.
BlockofStatementsAblockallowsthebuildingofunitswithoneormoreTransact-SQLstatements.
EveryblockbeginswiththeBEGINstatementandterminateswiththeENDstatement,asshowninthefollowingexample:Ch08.
indd2281/25/129:39:24AMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8Chapter8:StoredProceduresandUser-DefinedFunctions229BEGINstatement_1statement_2ENDAblockcanbeusedinsidetheIFstatementtoallowtheexecutionofmorethanonestatement,dependingonacertaincondition(seeExample8.
1).
IFStatementTheTransact-SQLstatementIFcorrespondstothestatementwiththesamenamethatissupportedbyalmostallprogramminglanguages.
IFexecutesoneTransact-SQLstatement(ormore,enclosedinablock)ifaBooleanexpression,whichfollowsthekeywordIF,evaluatestoTRUE.
IftheIFstatementcontainsanELSEstatement,asecondgroupofstatementscanbeexecutediftheBooleanexpressionevaluatestoFALSE.
NoteBeforeyoustarttoexecutebatches,storedprocedures,andUDFsinthischapter,pleasere-createtheentiresampledatabase.
Example8.
1USEsample;IF(SELECTCOUNT(*)FROMworks_onWHEREproject_no='p1'GROUPBYproject_no)>3PRINT'Thenumberofemployeesintheprojectp1is4ormore'ELSEBEGINPRINT'Thefollowingemployeesworkfortheprojectp1'SELECTemp_fname,emp_lnameFROMemployee,works_onWHEREemployee.
emp_no=works_on.
emp_noANDproject_no='p1'ENDCh08.
indd2291/25/129:39:24AM230MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8Example8.
1showstheuseofablockinsidetheIFstatement.
TheBooleanexpressionintheIFstatement,(SELECTCOUNT(*)FROMworks_onWHEREproject_no='p1'GROUPBYproject_no)>3isevaluatedtoTRUEforthesampledatabase.
Therefore,thesinglePRINTstatementintheIFpartisexecuted.
Noticethatthisexampleusesasubquerytoreturnthenumberofrows(usingtheCOUNTaggregatefunction)thatsatisfytheWHEREcondition(project_no='p1').
TheresultofExample8.
1isThenumberofemployeesintheprojectp1isfourormoreNoteTheELSEpartoftheIFstatementinExample8.
1containstwostatements:PRINTandSELECT.
Therefore,theblockwiththeBEGINandENDstatementsisrequiredtoenclosethetwostatements.
(ThePRINTstatementisanotherstatementthatbelongstoproceduralextensions;itreturnsauser-definedmessage.
)WHILEStatementTheWHILEstatementrepeatedlyexecutesoneTransact-SQLstatement(ormore,enclosedinablock)whiletheBooleanexpressionevaluatestoTRUE.
Inotherwords,iftheexpressionistrue,thestatement(orblock)isexecuted,andthentheexpressionisevaluatedagaintodetermineifthestatement(orblock)shouldbeexecutedagain.
ThisprocessrepeatsuntiltheexpressionevaluatestoFALSE.
AblockwithintheWHILEstatementcanoptionallycontainoneoftwostatementsusedtocontroltheexecutionofthestatementswithintheblock:BREAKorCONTINUE.
TheBREAKstatementstopstheexecutionofthestatementsinsidetheblockandstartstheexecutionofthestatementimmediatelyfollowingthisblock.
TheCONTINUEstatementstopsonlythecurrentexecutionofthestatementsintheblockandstartstheexecutionoftheblockfromitsbeginning.
Example8.
2showstheuseoftheWHILEstatement.
Example8.
2USEsample;WHILE(SELECTSUM(budget)FROMproject)240000BREAKELSECONTINUEENDInExample8.
2,thebudgetofallprojectswillbeincreasedby10percentuntilthesumofbudgetsisgreaterthan$500,000.
However,therepeatedexecutionwillbestoppedifthebudgetofoneoftheprojectsisgreaterthan$240,000.
TheexecutionofExample8.
2givesthefollowingoutput:(3rowsaffected)(3rowsaffected)(3rowsaffected)NoteIfyouwanttosuppresstheoutput,suchasthatinExample8.
2(indicatingthenumberofaffectedrowsinSQLstatements),usetheSETNOCOUNTONstatement.
LocalVariablesLocalvariablesareanimportantproceduralextensiontotheTransact-SQLlanguage.
Theyareusedtostorevalues(ofanytype)withinabatchoraroutine.
Theyare"local"becausetheycanbereferencedonlywithinthesamebatchinwhichtheyweredeclared.
(TheDatabaseEnginealsosupportsglobalvariables,whicharedescribedinChapter4.
)EverylocalvariableinabatchmustbedefinedusingtheDECLAREstatement.
(ForthesyntaxoftheDECLAREstatement,seeExample8.
3.
)Thedefinitionofeachvariablecontainsitsnameandthecorrespondingdatatype.
Variablesarealwaysreferencedinabatchusingtheprefix@.
TheassignmentofavaluetoalocalvariableisdoneUsingthespecialformoftheSELECTstatementCCUsingtheSETstatementCCDirectlyintheDECLAREstatementusingthe=sign(forinstance,@extra_CCbudgetMONEY=1500)TheusageofthefirsttwostatementsforavalueassignmentisdemonstratedinExample8.
3.
Ch08.
indd2311/25/129:39:24AM232MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8Example8.
3USEsample;DECLARE@avg_budgetMONEY,@extra_budgetMONEYSET@extra_budget=15000SELECT@avg_budget=AVG(budget)FROMprojectIF(SELECTbudgetFROMprojectWHEREproject_no='p1')<@avg_budgetBEGINUPDATEprojectSETbudget=budget+@extra_budgetWHEREproject_no='p1'PRINT'Budgetforp1increasedby@extra_budget'ENDELSEPRINT'Budgetforp1unchanged'TheresultisBudgetforp1increasedby@extra_budgetThebatchinExample8.
3calculatestheaverageofallprojectbudgetsandcomparesthisvaluewiththebudgetofprojectp1.
Ifthelattervalueissmallerthanthecalculatedvalue,thebudgetofprojectp1willbeincreasedbythevalueofthelocalvariable@extra_budget.
MiscellaneousProceduralStatementsTheproceduralextensionsoftheTransact-SQLlanguagealsocontainthefollowingstatements:RETURNCCGOTOCCRAISEERRORCCWAITFORCCTheRETURNstatementhasthesamefunctionalityinsideabatchastheBREAKstatementinsideWHILE.
ThismeansthattheRETURNstatementcausestheexecutionofthebatchtoterminateandthefirststatementfollowingtheendofthebatchtobeginexecuting.
Ch08.
indd2321/25/129:39:24AMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8Chapter8:StoredProceduresandUser-DefinedFunctions233TheGOTOstatementbranchestoalabel,whichstandsinfrontofaTransact-SQLstatementwithinabatch.
TheRAISEERRORstatementgeneratesauser-definederrormessageandsetsasystemerrorflag.
Auser-definederrornumbermustbegreaterthan50000.
(Allerrornumbers<=50000aresystemdefinedandarereservedbytheDatabaseEngine.
)Theerrorvaluesarestoredintheglobalvariable@@error.
(Example17.
3showstheuseoftheRAISEERRORstatement.
)TheWAITFORstatementdefineseitherthetimeinterval(iftheDELAYoptionisused)oraspecifiedtime(iftheTIMEoptionisused)thatthesystemhastowaitbeforeexecutingthenextstatementinthebatch.
ThesyntaxofthisstatementisWAITFOR{DELAY'time'|TIME'time'|TIMEOUT'timeout'}TheDELAYoptiontellsthedatabasesystemtowaituntilthespecifiedamountoftimehaspassed.
TIMEspecifiesatimeinoneoftheacceptableformatsfortemporaldata.
TIMEOUTspecifiestheamountoftime,inmilliseconds,towaitforamessagetoarriveinthequeue.
(Example13.
5showstheuseoftheWAITFORstatement.
)ExceptionHandlingwithTRY,CATCH,andTHROWVersionsofSQLServerprevioustoSQLServer2005requirederrorhandlingcodeaftereveryTransact-SQLstatementthatmightproduceanerror.
(Youcanhandleerrorsusingthe@@errorglobalvariable.
Example13.
1showstheuseofthisvariable.
)StartingwithSQLServer2005,youcancaptureandhandleexceptionsusingtwostatements,TRYandCATCH.
Thissectionfirstexplainswhat"exception"meansandthendiscusseshowthesetwostatementswork.
Anexceptionisaproblem(usuallyanerror)thatpreventsthecontinuationofaprogram.
Withsuchaproblem,youcannotcontinueprocessingbecausethereisnotenoughinformationneededtohandletheproblem.
Forthisreason,theexistingproblemwillberelegatedtoanotherpartoftheprogram,whichwillhandletheexception.
TheroleoftheTRYstatementistocapturetheexception.
(Becausethisprocessusuallycomprisesseveralstatements,theterm"TRYblock"typicallyisusedinsteadof"TRYstatement.
")IfanexceptionoccurswithintheTRYblock,thepartofthesystemcalledtheexceptionhandlerdeliverstheexceptiontotheotherpartoftheprogram,whichwillhandletheexception.
ThisprogrampartisdenotedbythekeywordCATCHandisthereforecalledtheCATCHblock.
NoteExceptionhandlingusingtheTRYandCATCHstatementsisthecommonwaythatmodernprogramminglanguageslikeC#andJavatreaterrors.
Ch08.
indd2331/25/129:39:24AM234MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8ExceptionhandlingwiththeTRYandCATCHblocksgivesaprogrammeralotofbenefits,suchas:ExceptionsprovideacleanwaytocheckforerrorswithoutclutteringcodeCCExceptionsprovideamechanismtosignalerrorsdirectlyratherthanusingsomeCCsideeffectsExceptionscanbeseenbytheprogrammerandcheckedduringthecompilationCCprocessSQLServer2012introducesthethirdstatementinrelationtohandlingerrors:THROW.
Thisstatementallowsyoutothrowanexceptioncaughtintheexceptionhandlingblock.
Simplystated,theTHROWstatementisanotherreturnmechanism,whichbehavessimilarlytothealreadydescribedRAISEERRORstatement.
Example8.
4showshowexceptionhandlingwiththeTRY/CATCH/THROWworks.
Itshowshowyoucanuseexceptionhandlingtoinsertallstatementsinabatchortorollbacktheentirestatementgroupifanerroroccurs.
Theexampleisbasedonthereferentialintegritybetweenthedepartmentandemployeetables.
Forthisreason,youhavetocreatebothtablesusingthePRIMARYKEYandFOREIGNKEYconstraints,asdoneinExample5.
11.
Example8.
4USEsample;BEGINTRYBEGINTRANSACTIONinsertintoemployeevalues(11111,'Ann','Smith','d2');insertintoemployeevalues(22222,'Matthew','Jones','d4');--referentialintegrityerrorinsertintoemployeevalues(33333,'John','Barrimore','d2');COMMITTRANSACTIONPRINT'Transactioncommitted'ENDTRYBEGINCATCHROLLBACKPRINT'Transactionrolledback';THROWENDCATCHAftertheexecutionofthebatchinExample8.
4,allthreestatementsinthebatchwon'tbeexecutedatall,andtheoutputofthisexampleisCh08.
indd2341/25/129:39:24AMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8Chapter8:StoredProceduresandUser-DefinedFunctions235TransactionrolledbackMsg547,Level16,State0,Line4TheINSERTstatementconflictedwiththeFOREIGNKEYconstraint"foreign_emp".
Theconflictoccurredindatabase"sample",table"dbo.
department",column'dept_no'.
TheexecutionofExample8.
4worksasfollows.
ThefirstINSERTstatementisexecutedsuccessfully.
Then,thesecondstatementcausesthereferentialintegrityerror.
BecauseallthreestatementsarewritteninsidetheTRYblock,theexceptionis"thrown"andtheexceptionhandlerstartstheCATCHblock.
CATCHrollsbackallstatementsandprintsthecorrespondingmessage.
AfterthattheTHROWstatementreturnstheexecutionofthebatchtothecaller.
Forthisreason,thecontentoftheemployeetablewon'tchange.
NoteThestatementsBEGINTRANSACTION,COMMITTRANSACTION,andROLLBACKareTransact-SQLstatementsconcerningtransactions.
Thesestatementsstart,commit,androllbacktransactions,respectively.
SeeChapter13forthediscussionofthesestatementsandtransactionsgenerally.
Example8.
5showsthebatchthatsupportsserver-sidepaging(forthedescriptionofserver-sidepaging,seeChapter6).
Example8.
5USEAdventureWorks;DECLARE@PageSizeTINYINT=20,@CurrentPageINT=4;SELECTBusinessEntityID,JobTitle,BirthDateFROMHumanResources.
EmployeeWHEREGender='F'ORDERBYJobTitleOFFSET(@PageSize*(@CurrentPage-1))ROWSFETCHNEXT@PageSizeROWSONLY;ThebatchinExample8.
5usestheAdventureWorksdatabaseanditsEmployeetabletoshowhowgenericserver-sidepagingcanbeimplemented.
The@PagesizevariableisusedwiththeFETCHNEXTstatementtospecifythenumberofrowsperpage(20,inthiscase).
Theothervariable,@CurrentPage,specifieswhichparticularpageshouldbedisplayed.
Inthisexample,thecontentofthethirdpagewillbedisplayed.
Ch08.
indd2351/25/129:39:24AM236MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8StoredProceduresAstoredprocedureisaspecialkindofbatchwritteninTransact-SQL,usingtheSQLlanguageanditsproceduralextensions.
Themaindifferencebetweenabatchandastoredprocedureisthatthelatterisstoredasadatabaseobject.
Inotherwords,storedproceduresaresavedontheserversidetoimprovetheperformanceandconsistencyofrepetitivetasks.
TheDatabaseEnginesupportsstoredproceduresandsystemprocedures.
Storedproceduresarecreatedinthesamewayasallotherdatabaseobjects—thatis,byusingtheDDL.
SystemproceduresareprovidedwiththeDatabaseEngineandcanbeusedtoaccessandmodifytheinformationinthesystemcatalog.
Thissectiondescribes(user-defined)storedprocedures,whilesystemproceduresareexplainedinthenextchapter.
Whenastoredprocedureiscreated,anoptionallistofparameterscanbedefined.
Theprocedureacceptsthecorrespondingargumentseachtimeitisinvoked.
Storedprocedurescanoptionallyreturnavalue,whichdisplaystheuser-definedinformationor,inthecaseofanerror,thecorrespondingerrormessage.
Astoredprocedureisprecompiledbeforeitisstoredasanobjectinthedatabase.
Theprecompiledformisstoredinthedatabaseandusedwheneverthestoredprocedureisexecuted.
Thispropertyofstoredproceduresoffersanimportantbenefit:therepeatedcompilationofaprocedureis(almostalways)eliminated,andtheexecutionperformanceisthereforeincreased.
Thispropertyofstoredproceduresoffersanotherbenefitconcerningthevolumeofdatathatmustbesenttoandfromthedatabasesystem.
Itmighttakelessthan50bytestocallastoredprocedurecontainingseveralthousandbytesofstatements.
Theaccumulatedeffectofthissavingswhenmultipleusersareperformingrepetitivetaskscanbequitesignificant.
Storedprocedurescanalsobeusedforthefollowingpurposes:TocontrolaccessauthorizationCCTocreateanaudittrailofactivitiesindatabasetablesCCTheuseofstoredproceduresprovidessecuritycontrolaboveandbeyondtheuseoftheGRANTandREVOKEstatements(seeChapter12),whichdefinedifferentaccessprivilegesforauser.
Thisisbecausetheauthorizationtoexecuteastoredprocedureisindependentoftheauthorizationtomodifytheobjectsthatthestoredprocedurecontains,asdescribedinthenextsection.
Storedproceduresthatauditwriteand/orreadoperationsconcerningatableareanadditionalsecurityfeatureofthedatabase.
Withtheuseofsuchprocedures,thedatabaseadministratorcantrackmodificationsmadebyusersorapplicationprograms.
Ch08.
indd2361/25/129:39:24AMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8Chapter8:StoredProceduresandUser-DefinedFunctions237CreationandExecutionofStoredProceduresStoredproceduresarecreatedwiththeCREATEPROCEDUREstatement,whichhasthefollowingsyntax:CREATEPROC[EDURE][schema_name.
]proc_name[({@param1}type1[VARYING][=default1][OUTPUT][WITH{RECOMPILE|ENCRYPTION|EXECUTEAS'user_name'}][FORREPLICATION]ASbatch|EXTERNALNAMEmethod_nameschema_nameisthenameoftheschematowhichtheownershipofthecreatedstoredprocedureisassigned.
proc_nameisthenameofthenewstoredprocedure.
@param1isaparameter,whiletype1specifiesitsdatatype.
Theparameterinastoredprocedurehasthesamelogicalmeaningasthelocalvariableforabatch.
Parametersarevaluespassedfromthecallerofthestoredprocedureandareusedwithinthestoredprocedure.
default1specifiestheoptionaldefaultvalueofthecorrespondingparameter.
(DefaultcanalsobeNULL.
)TheOUTPUToptionindicatesthattheparameterisareturnparameterandcanbereturnedtothecallingprocedureortothesystem(seeExample8.
9laterinthissection).
Asyoualreadyknow,theprecompiledformofaprocedureisstoredinthedatabaseandusedwheneverthestoredprocedureisexecuted.
Ifyouwanttogeneratethecompiledformeachtimetheprocedureisexecuted,usetheWITHRECOMPILEoption.
NoteTheuseoftheWITHRECOMPILEoptiondestroysoneofthemostimportantbenefitsofthestoredprocedures:theperformanceadvantagegainedbyasingleprecompilation.
Forthisreason,theWITHRECOMPILEoptionshouldbeusedonlywhendatabaseobjectsusedbythestoredprocedurearemodifiedfrequentlyorwhentheparametersusedbythestoredprocedurearevolatile.
TheEXECUTEASclausespecifiesthesecuritycontextunderwhichtoexecutethestoredprocedureafteritisaccessed.
Byspecifyingthecontextinwhichtheprocedureisexecuted,youcancontrolwhichuseraccounttheDatabaseEngineusestovalidatepermissionsonobjectsreferencedbytheprocedure.
Bydefault,onlythemembersofthesysadminfixedserverrole,andthedb_owneranddb_ddladminfixeddatabaseroles,canusetheCREATEPROCEDUREstatement.
However,themembersoftheserolesmayassignthisprivilegetootherusersCh08.
indd2371/25/129:39:24AM238MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8byusingtheGRANTCREATEPROCEDUREstatement.
(Forthediscussionofuserpermissions,fixedserverroles,andfixeddatabaseroles,seeChapter12.
)Example8.
6showsthecreationofthesimplestoredprocedurefortheprojecttable.
Example8.
6USEsample;GOCREATEPROCEDUREincrease_budget(@percentINT=5)ASUPDATEprojectSETbudget=budget+budget*@percent/100;NoteTheGOstatementisusedtoseparatetwobatches.
(TheCREATEPROCEDUREstatementmustbethefirststatementinthebatch.
)Thestoredprocedureincrease_budgetincreasesthebudgetsofallprojectsforacertainpercentagevaluethatisdefinedusingtheparameter@percent.
Theprocedurealsodefinesthedefaultvalue(5),whichisusedifthereisnoargumentattheexecutiontimeoftheprocedure.
NoteItispossibletocreatestoredproceduresthatreferencenonexistenttables.
Thisfeatureallowsyoutodebugprocedurecodewithoutcreatingtheunderlyingtablesfirst,orevenconnectingtothetargetserver.
Incontrastto"base"storedproceduresthatareplacedinthecurrentdatabase,itispossibletocreatetemporarystoredproceduresthatarealwaysplacedinthetemporarysystemdatabasecalledtempdb.
Youmightcreateatemporarystoredproceduretoavoidexecutingaparticulargroupofstatementsrepeatedlywithinaconnection.
Youcancreatelocalorglobaltemporaryproceduresbyprecedingtheprocedurenamewithasinglepoundsign(#proc_name)forlocaltemporaryproceduresandadoublepoundsign(##proc_name,forexample)forglobaltemporaryprocedures.
Alocaltemporarystoredprocedurecanbeexecutedonlybytheuserwhocreatedit,andonlyduringthesameconnection.
Aglobaltemporaryprocedurecanbeexecutedbyallusers,butonlyuntilthelastconnectionexecutingit(usuallythecreator's)ends.
Thelifecycleofastoredprocedurehastwophases:itscreationanditsexecution.
Eachprocedureiscreatedonceandexecutedmanytimes.
TheEXECUTEstatementexecutesanexistingprocedure.
TheexecutionofastoredprocedureisallowedforeachCh08.
indd2381/25/129:39:24AMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8Chapter8:StoredProceduresandUser-DefinedFunctions239userwhoeitheristheowneroforhastheEXECUTEprivilegefortheprocedure(seeChapter12).
TheEXECUTEstatementhasthefollowingsyntax:[[EXEC[UTE]][@return_status=]{proc_name|@proc_name_var}parameter1=]value|[@parameter1=]@variable[OUTPUT]]|DEFAULT}.
.
[WITHRECOMPILE]AlloptionsintheEXECUTEstatement,otherthanreturn_status,havetheequivalentlogicalmeaningastheoptionswiththesamenamesintheCREATEPROCEDUREstatement.
return_statusisanoptionalintegervariablethatstoresthereturnstatusofaprocedure.
Thevalueofaparametercanbeassignedusingeitheravalue(value)oralocalvariable(@variable).
Theorderofparametervaluesisnotrelevantiftheyarenamed,butiftheyarenotnamed,parametervaluesmustbesuppliedintheorderdefinedintheCREATEPROCEDUREstatement.
TheDEFAULTclausesuppliesthedefaultvalueoftheparameterasdefinedintheprocedure.
WhentheprocedureexpectsavalueforaparameterthatdoesnothaveadefineddefaultandeitheraparameterismissingortheDEFAULTkeywordisspecified,anerroroccurs.
NoteWhentheEXECUTEstatementisthefirststatementinabatch,theword"EXECUTE"canbeomittedfromthestatement.
Despitethis,itwouldbesafertoincludethiswordineverybatchyouwrite.
Example8.
7showstheuseoftheEXECUTEstatement.
Example8.
7USEsample;EXECUTEincrease_budget10;TheEXECUTEstatementinExample8.
7executesthestoredprocedureincrease_budget(Example8.
6)andincreasesthebudgetsofallprojectsby10percenteach.
Example8.
8showsthecreationofaprocedurethatreferencesthetablesemployeeandworks_on.
Example8.
8USEsample;GOCREATEPROCEDUREmodify_empno(@old_noINTEGER,@new_noINTEGER)ASUPDATEemployeeSETemp_no=@new_noCh08.
indd2391/25/129:39:24AM240MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8WHEREemp_no=@old_noUPDATEworks_onSETemp_no=@new_noWHEREemp_no=@old_noTheproceduremodify_empnoinExample8.
8demonstratestheuseofstoredproceduresaspartofthemaintenanceofthereferentialintegrity(inthiscase,betweentheemployeeandworks_ontables).
Suchastoredprocedurecanbeusedinsidethedefinitionofatrigger,whichactuallymaintainsthereferentialintegrity(seeExample14.
3).
Example8.
9showstheuseoftheOUTPUTclause.
Example8.
9USEsample;GOCREATEPROCEDUREdelete_emp@employee_noINT,@counterINTOUTPUTASSELECT@counter=COUNT(*)FROMworks_onWHEREemp_no=@employee_noDELETEFROMemployeeWHEREemp_no=@employee_noDELETEFROMworks_onWHEREemp_no=@employee_noThisstoredprocedurecanbeexecutedusingthefollowingstatements:DECLARE@quantityINTEXECUTEdelete_emp@employee_no=28559,@counter=@quantityOUTPUTTheprecedingexamplecontainsthecreationofthedelete_empprocedureaswellasitsexecution.
Thisprocedurecalculatesthenumberofprojectsonwhichtheemployee(withtheemployeenumber@employee_no)works.
Thecalculatedvalueisthenassignedtothe@counterparameter.
Afterthedeletionofallrowswiththeassignedemployeenumberfromtheemployeeandworks_ontables,thecalculatedvaluewillbeassignedtothe@quantityvariable.
NoteThevalueoftheparameterwillbereturnedtothecallingprocedureiftheOUTPUToptionisused.
InExample8.
9,thedelete_empprocedurepassesthe@counterparametertothecallingstatement,sotheprocedurereturnsthevaluetothesystem.
Therefore,the@counterparametermustbedeclaredwiththeOUTPUToptionintheprocedureaswellasintheEXECUTEstatement.
Ch08.
indd2401/25/129:39:25AMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8Chapter8:StoredProceduresandUser-DefinedFunctions241TheEXECUTEStatementwithRESULTSETSClauseSQLServer2012introducestheWITHRESULTSETSclausefortheEXECUTEstatement.
Usingthisclause,youcanchangeconditionallytheformoftheresultsetofastoredprocedure.
Thefollowingtwoexampleshelptoexplainthisclause.
Example8.
10isanintroductoryexamplethatshowshowtheoutputlookswhentheWITHRESULTSETSclauseisomitted.
Example8.
10USEsample;GOCREATEPROCEDUREemployees_in_dept(@deptCHAR(4))ASSELECTemp_no,emp_lnameFROMemployeeWHEREdept_noIN(SELECT@deptFROMdepartmentGROUPBYdept_no)employees_in_deptisasimplestoredprocedurethatdisplaysthenumbersandfamilynamesofallemployeesworkingforaparticulardepartment.
(Thedepartmentnumberisaparameteroftheprocedureandmustbespecifiedwhentheprocedureisinvoked.
)Theresultofthisprocedureisatablewithtwocolumns,namedaccordingtothenamesofthecorrespondingcolumns(emp_noandemp_lname).
Tochangethesenames(andtheirdatatypes,too),SQLServer2012supportsthenewWITHRESULTSSETSclause.
Example8.
11showstheuseofthisclause.
Example8.
11USEsample;EXECemployees_in_dept'd1'WITHRESULTSETS(([EMPLOYEENUMBER]INTNOTNULL,[NAMEOFEMPLOYEE]CHAR(20)NOTNULL));TheoutputisEMPLOYEENUMBERNAMEOFEMPLOYEE18316Barrimore28559MoserCh08.
indd2411/25/129:39:25AM242MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8Asyoucansee,theWITHRESULTSETSclauseinExample8.
11allowsyoutochangethenameanddatatypesofcolumnsdisplayedintheresultset.
Therefore,thisnewfunctionalitygivesyoutheflexibilitytoexecutestoredproceduresandplacetheoutputresultsetsintoanewtable.
ChangingtheStructureofStoredProceduresTheDatabaseEnginealsosupportstheALTERPROCEDUREstatement,whichmodifiesthestructureofastoredprocedure.
TheALTERPROCEDUREstatementisusuallyusedtomodifyTransact-SQLstatementsinsideaprocedure.
AlloptionsoftheALTERPROCEDUREstatementcorrespondtotheoptionswiththesamenameintheCREATEPROCEDUREstatement.
Themainpurposeofthisstatementistoavoidreassignmentofexistingprivilegesforthestoredprocedure.
NoteTheDatabaseEnginesupportstheCURSORdatatype.
Youusethisdatatypetodeclarecursorsinsideastoredprocedure.
Acursorisaprogrammingconstructthatisusedtostoretheoutputofaquery(usuallyasetofrows)andtoallowend-userapplicationstodisplaytherowsrecordbyrecord.
Adetaileddiscussionofcursorsisoutsideofthescopeofthisbook.
Astoredprocedure(oragroupofstoredprocedureswiththesamename)isremovedusingtheDROPPROCEDUREstatement.
Onlytheownerofthestoredprocedureandthemembersofthedb_ownerandsysadminfixedrolescanremovetheprocedure.
StoredProceduresandCLRSQLServersupportstheCommonLanguageRuntime(CLR),whichallowsyoutodevelopdifferentdatabaseobjects(storedprocedures,user-definedfunctions,triggers,user-definedaggregates,anduser-definedtypes)usingC#andVisualBasic.
CLRalsoallowsyoutoexecutethesedatabaseobjectsusingthecommonrun-timesystem.
NoteYouenableanddisabletheuseofCLRthroughtheclr_enabledoptionofthesp_configuresystemprocedure.
ExecutetheRECONFIGUREstatementtoupdatetherunningconfigurationvalue.
Example8.
12showshowyoucanenabletheuseofCLRwiththesp_configuresystemprocedure.
Ch08.
indd2421/25/129:39:25AMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8Chapter8:StoredProceduresandUser-DefinedFunctions243Example8.
12usesample;EXECsp_configure'clr_enabled',1RECONFIGUREToimplement,compile,andstoreproceduresusingCLR,youhavetoexecutethefollowingfourstepsinthegivenorder:ImplementastoredprocedureusingC#(orVisualBasic)andcompilethe1.
program,usingthecorrespondingcompiler.
UsetheCREATEASSEMBLYstatementtocreatethecorrespondingexecutable2.
file.
StoretheprocedureasaserverobjectusingtheCREATEPROCEDURE3.
statement.
ExecutetheprocedureusingtheEXECUTEstatement.
4.
Figure8-1showshowCLRworks.
YouuseadevelopmentenvironmentsuchasVisualStudiotoimplementyourprogram.
Aftertheimplementation,starttheC#orFigure8-1TheflowdiagramfortheexecutionofaCLRstoredprocedureExecutablecodeProcedureasdatabaseobjectObjectcodeSourcecodeCLRcompilerCREATEASSEMBLYstatementCREATEPROCEDUREstatementCh08.
indd2431/25/129:39:25AM244MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8VisualBasiccompilertogeneratetheobjectcode.
Thiscodewillbestoredina.
dllfile,whichisthesourcefortheCREATEASSEMBLYstatement.
Aftertheexecutionofthisstatement,yougettheintermediatecode.
InthenextstepyouusetheCREATEPROCEDUREstatementtostoretheexecutableasadatabaseobject.
Finally,thestoredprocedurecanbeexecutedusingthealready-introducedEXECUTEstatement.
Examples8.
13through8.
17demonstratethewholeprocessjustdescribed.
Example8.
13showstheC#programthatwillbeusedtodemonstratehowyouapplyCLRtoimplementanddeploystoredprocedures.
Example8.
13usingSystem;usingSystem.
Data;usingSystem.
Data.
Sql;usingSystem.
Data.
SqlClient;usingMicrosoft.
SqlServer.
Server;usingSystem.
Data.
SqlTypes;publicpartialclassStoredProcedures{[SqlProcedure]publicstaticintGetEmployeeCount(){intiRows;SqlConnectionconn=newSqlConnection("ContextConnection=true");conn.
Open();SqlCommandsqlCmd=conn.
CreateCommand();sqlCmd.
CommandText="selectcount(*)as'EmployeeCount'"+"fromemployee";iRows=(int)sqlCmd.
ExecuteScalar();conn.
Close();returniRows;}};Thisprogramusesaquerytocalculatethenumberofrowsintheemployeetable.
Theusingdirectivesatthebeginningoftheprogramspecifynamespaces,suchasSystem.
Data.
Thesedirectivesallowyoutospecifyclassnamesinthesourceprogramwithoutreferencingthecorrespondingnamespace.
TheStoredProceduresclassisthendefined,whichiswrittenwitha[SqlProcedure]attribute.
Thisattributetellsthecompilerthattheclassisastoredprocedure.
InsidethatclassisdefinedamethodcalledGetEmployeeCount().
TheconnectiontothedatabasesystemisestablishedusingtheconninstanceoftheSQLConnectionclass.
TheOpen()methodisappliedtothatCh08.
indd2441/25/129:39:25AMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8Chapter8:StoredProceduresandUser-DefinedFunctions245instancetoopentheconnection.
TheCreateCommand()method,appliedtoconn,allowsyoutoaccesstheSqlCommandinstancecalledsqlCmd.
ThefollowinglinesofcodesqlCmd.
CommandText="selectcount(*)as'EmployeeCount'"+"fromemployee";iRows=(int)sqlCmd.
ExecuteScalar();usetheSELECTstatementtofindthenumberofrowsintheemployeetableandtodisplaytheresult.
ThecommandtextisspecifiedbysettingtheCommandTextpropertyoftheSqlCmdinstancereturnedbythecalltotheCreateCommand()method.
Next,theExecuteScalar()methodoftheSqlCommandinstanceiscalled.
Thisreturnsascalarvalue,whichisfinallyconvertedtotheintdatatypeandassignedtotheiRowsvariable.
Example8.
14showsthefirststepindeployingstoredproceduresusingCLR.
Example8.
14csc/target:libraryGetEmployeeCount.
cs/reference:"C:\ProgramFiles\MicrosoftSQLServer\MSSQL11.
MSSQLSERVER\MSSQL\Binn\sqlaccess.
dll"Example8.
14demonstrateshowtocompiletheC#methodcalledGetEmployeeCount()(Example8.
13).
(Actually,thiscommandcanbeusedgenerallytocompileanyC#program,ifyousettheappropriatenameforthesourceprogram.
)cscisthecommandthatisusedtoinvoketheC#compiler.
YouinvokethecsccommandattheWindowscommandline.
Beforestartingthecommand,youhavetospecifythelocationofthecompilerusingthePATHenvironmentvariable.
Atthetimeofwritingthisbook,theC#compiler(thecsc.
exefile)canbefoundintheC:\WINDOWS\Microsoft.
NET\Frameworkdirectory.
(Youshouldselecttheappropriateversionofthecompiler.
)The/targetoptionspecifiesthenameoftheC#program,whilethe/referenceoptiondefinesthe.
dllfile,whichisnecessaryforthecompilationprocess.
Example8.
15showsthenextstepincreatingthestoredprocedure.
(Beforeyouexecutethisexample,copytheexisting.
dllfiletotherootoftheC:drive.
)Example8.
15USEsample;GOCREATEASSEMBLYGetEmployeeCountFROM'C:\GetEmployeeCount.
dll'WITHPERMISSION_SET=SAFECh08.
indd2451/25/129:39:25AM246MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8TheCREATEASSEMBLYstatementusesthemanagedcodeasthesourcetocreatethecorrespondingobject,againstwhichCLRstoredprocedures,UDFs,andtriggerscanbecreated.
Thisstatementhasthefollowingsyntax:CREATEASSEMBLYassembly_name[AUTHORIZATIONowner_name]FROM{dll_file}[WITHPERMISSION_SET={SAFE|EXTERNAL_ACCESS|UNSAFE}]assembly_nameisthenameoftheassembly.
TheoptionalAUTHORIZATIONclausespecifiesthenameofaroleasowneroftheassembly.
TheFROMclausespecifiesthepathwheretheassemblybeinguploadedislocated.
(Example8.
15copiesthe.
dllfilegeneratedfromthesourceprogramfromtheFrameworkdirectorytotherootoftheC:drive.
)TheWITHPERMISSION_SETclauseisaveryimportantclauseoftheCREATEASSEMBLYstatementandshouldalwaysbeset.
Itspecifiesasetofcodeaccesspermissionsgrantedtotheassembly.
SAFEisthemostrestrictivepermissionset.
Codeexecutedbyanassemblywiththispermissioncannotaccessexternalsystemresources,suchasfiles.
EXTERNAL_ACCESSallowsassembliestoaccesscertainexternalsystemresources,whileUNSAFEallowsunrestrictedaccesstoresources,bothwithinandoutsidethedatabasesystem.
NoteInordertostoretheinformationconcerningassemblycode,ausermusthavetheabilitytoexecutetheCREATEASSEMBLYstatement.
Theuser(orrole)executingthestatementistheowneroftheassembly.
ItispossibletoassignanassemblytoanotheruserbyusingtheAUTHORIZATIONclauseoftheCREATESCHEMAstatement.
TheDatabaseEnginealsosupportstheALTERASSEMBLYandDROPASSEMBLYstatements.
YoucanusetheALTERASSEMBLYstatementtorefreshthesystemcatalogtothelatestcopyof.
NETmodulesholdingitsimplementation.
Thisstatementalsoaddsorremovesfilesassociatedwiththecorrespondingassembly.
TheDROPASSEMBLYstatementremovesthespecifiedassemblyandallitsassociatedfilesfromthecurrentdatabase.
Example8.
16createsthestoredproceduresbasedonthemanagedcodeimplementedinExample8.
13.
Example8.
16USEsample;GOCREATEPROCEDUREGetEmployeeCountASEXTERNALNAMEGetEmployeeCount.
StoredProcedures.
GetEmployeeCountCh08.
indd2461/25/129:39:25AMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8Chapter8:StoredProceduresandUser-DefinedFunctions247TheCREATEPROCEDUREstatementinExample8.
16isdifferentfromthesamestatementusedinExamples8.
6and8.
8,becauseitcontainstheEXTERNALNAMEoption.
ThisoptionspecifiesthatthecodeisgeneratedusingCLR.
Thenameinthisclauseisathree-partname:assembly_name.
class_name.
method_nameassembly_nameCCisthenameoftheassembly(seeExample8.
15).
class_nameCCisthenameofthepublicclass(seeExample8.
13).
method_nameCC,whichisoptional,isthenameofthemethod,whichisspecifiedinsidetheclass.
Example8.
17isusedtoexecutetheGetEmployeeCountprocedure.
Example8.
17USEsample;DECLARE@retINTEXECUTE@ret=GetEmployeeCountPRINT@retThePRINTstatementreturnsthecurrentnumberoftherowsintheemployeetable.
User-DefinedFunctionsInprogramminglanguages,therearegenerallytwotypesofroutines:StoredproceduresCCUser-definedfunctions(UDFs)CCAsdiscussedinthepreviousmajorsectionofthischapter,storedproceduresaremadeupofseveralstatementsthathavezeroormoreinputparametersbutusuallydonotreturnanyoutputparameters.
Incontrast,functionsalwayshaveonereturnvalue.
ThissectiondescribesthecreationanduseofUDFs.
Ch08.
indd2471/25/129:39:26AM248MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8CreationandExecutionofUser-DefinedFunctionsUDFsarecreatedwiththeCREATEFUNCTIONstatement,whichhasthefollowingsyntax:CREATEFUNCTION[schema_name.
]function_nameparam}type[=default]RETURNS{scalar_type|[@variable]TABLE}[WITH{ENCRYPTION|SCHEMABINDING}[AS]{block|RETURN(select_statement)}schema_nameisthenameoftheschematowhichtheownershipofthecreatedUDFisassigned.
function_nameisthenameofthenewfunction.
@paramisaninputparameter,whiletypespecifiesitsdatatype.
ParametersarevaluespassedfromthecalleroftheUDFandareusedwithinthefunction.
defaultspecifiestheoptionaldefaultvalueofthecorrespondingparameter.
(DefaultcanalsobeNULL.
)TheRETURNSclausedefinesadatatypeofthevaluereturnedbytheUDF.
Thisdatatypecanbeanyofthestandarddatatypessupportedbythedatabasesystem,includingtheTABLEdatatype.
(TheonlystandarddatatypethatyoucannotuseisTIMESTAMP.
)UDFsareeitherscalar-valuedortable-valued.
Ascalar-valuedfunctionreturnsanatomic(scalar)value.
ThismeansthatintheRETURNSclauseofascalar-valuedfunction,youspecifyoneofthestandarddatatypes.
Functionsaretable-valuediftheRETURNSclausereturnsasetofrows(seethenextsubsection).
TheWITHENCRYPTIONoptionencryptstheinformationinthesystemcatalogthatcontainsthetextoftheCREATEFUNCTIONstatement.
Inthatcase,youcannotviewthetextusedtocreatethefunction.
(Usethisoptiontoenhancethesecurityofyourdatabasesystem.
)Thealternativeclause,WITHSCHEMABINDING,bindstheUDFtothedatabaseobjectsthatitreferences.
Anyattempttomodifythestructureofthedatabaseobjectthatthefunctionreferencesfails.
(Thebindingofthefunctiontothedatabaseobjectsitreferencesisremovedonlywhenthefunctionisaltered,sotheSCHEMABINDINGoptionisnolongerspecified.
)DatabaseobjectsthatarereferencedbyafunctionmustfulfillthefollowingconditionsifyouwanttousetheSCHEMABINDINGclauseduringthecreationofthatfunction:AllviewsandUDFsreferencedbythefunctionmustbeschema-bound.
CCAlldatabaseobjects(tables,views,orUDFs)mustbeinthesamedatabaseasCCthefunction.
Ch08.
indd2481/25/129:39:26AMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8Chapter8:StoredProceduresandUser-DefinedFunctions249blockistheBEGIN/ENDblockthatcontainstheimplementationofthefunction.
ThefinalstatementoftheblockmustbeaRETURNstatementwithanargument.
(Thevalueoftheargumentisthevaluereturnedbythefunction.
)InthebodyofaBEGIN/ENDblock,onlythefollowingstatementsareallowed:AssignmentstatementssuchasSETCCControl-of-flowstatementssuchasWHILEandIFCCDECLAREstatementsdefininglocaldatavariablesCCSELECTstatementscontainingSELECTlistswithexpressionsthatassigntoCCvariablesthatarelocaltothefunctionINSERT,UPDATE,andDELETEstatementsmodifyingvariablesoftheCCTABLEdatatypethatarelocaltothefunctionBydefault,onlythemembersofthesysadminfixedserverroleandthedb_owneranddb_ddladminfixeddatabaserolescanusetheCREATEFUNCTIONstatement.
However,themembersoftheserolesmayassignthisprivilegetootherusersbyusingtheGRANTCREATEFUNCTIONstatement(seeChapter12).
Example8.
18showsthecreationofthefunctioncalledcompute_costs.
Example8.
18--Thisfunctioncomputesadditionaltotalcoststhatarise--ifbudgetsofprojectsincreaseUSEsample;GOCREATEFUNCTIONcompute_costs(@percentINT=10)RETURNSDECIMAL(16,2)BEGINDECLARE@additional_costsDEC(14,2),@sum_budgetdec(16,2)SELECT@sum_budget=SUM(budget)FROMprojectSET@additional_costs=@sum_budget*@percent/100RETURN@additional_costsENDThefunctioncompute_costscomputesadditionalcoststhatarisewhenallbudgetsofprojectsincrease.
Thesingleinputvariable,@percent,specifiesthepercentageofincreaseofbudgets.
TheBEGIN/ENDblockfirstdeclarestwolocalvariables:@additional_costsand@sum_budget.
Thefunctionthenassignsto@sum_budgetthesumofallbudgets,usingtheSELECTstatement.
Afterthat,thefunctioncomputestotaladditionalcostsandreturnsthisvalueusingtheRETURNstatement.
Ch08.
indd2491/25/129:39:26AM250MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8InvokingUser-DefinedFunctionsEachUDFcanbeinvokedinTransact-SQLstatements,suchasSELECT,INSERT,UPDATE,orDELETE.
Toinvokeafunction,specifythenameofit,followedbyparentheses.
Withintheparentheses,youcanspecifyoneormorearguments.
Argumentsarevaluesorexpressionsthatarepassedtotheinputparametersthataredefinedimmediatelyafterthefunctionname.
Whenyouinvokeafunction,andallparametershavenodefaultvalues,youmustsupplyargumentvaluesforalloftheparametersandyoumustspecifytheargumentvaluesinthesamesequenceinwhichtheparametersaredefinedintheCREATEFUNCTIONstatement.
Example8.
19showstheuseofthecompute_costsfunction(Example8.
18)inaSELECTstatement.
Example8.
19USEsample;SELECTproject_no,project_nameFROMprojectWHEREbudgetcompute_costs(25)Theresultisproject_noproject_namep2GeminiTheSELECTstatementinExample8.
19displaysnamesandnumbersofallprojectswherethebudgetislowerthanthetotaladditionalcostsofallprojectsforagivenpercentage.
NoteEachfunctionusedinaTransact-SQLstatementmustbespecifiedusingitstwo-partname—thatis,schema_name.
function_name.
Table-ValuedFunctionsAsyoualreadyknow,functionsaretable-valuediftheRETURNSclausereturnsasetofrows.
Dependingonhowthebodyofthefunctionisdefined,table-valuedfunctionscanbeclassifiedasinlineormultistatementfunctions.
IftheRETURNSclausespecifiesTABLEwithnoaccompanyinglistofcolumns,thefunctionisaninlinefunction.
InlinefunctionsreturntheresultsetofaSELECTstatementasavariableCh08.
indd2501/25/129:39:26AMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8Chapter8:StoredProceduresandUser-DefinedFunctions251oftheTABLEdatatype(seeExample8.
20).
Amultistatementtable-valuedfunctionincludesanamefollowedbyTABLE.
(ThenamedefinesaninternalvariableofthetypeTABLE.
)Youcanusethisvariabletoinsertrowsintoitandthenreturnthevariableasthereturnvalueofthefunction.
Example8.
20showsafunctionthatreturnsavariableoftheTABLEdatatype.
Example8.
20USEsample;GOCREATEFUNCTIONemployees_in_project(@pr_numberCHAR(4))RETURNSTABLEASRETURN(SELECTemp_fname,emp_lnameFROMworks_on,employeeWHEREemployee.
emp_no=works_on.
emp_noANDproject_no=@pr_number)Theemployees_in_projectfunctionisusedtodisplaynamesofallemployeesthatbelongtoaparticularproject.
Theinputparameter@pr_numberspecifiesaprojectnumber.
Whilethefunctiongenerallyreturnsasetofrows,theRETURNSclausecontainstheTABLEdatatype.
(NotethattheBEGIN/ENDblockinExample8.
20mustbeomitted,whiletheRETURNclausecontainsaSELECTstatement.
)Example8.
21showstheuseoftheemployees_in_projectfunction.
Example8.
21USEsample;SELECT*FROMemployees_in_project('p3')Theresultisemp_fnameemp_lnameAnnJonesElsaBertoniElkeHanselTable-ValuedFunctionsandAPPLYTheAPPLYoperatorisarelationaloperatorthatallowsyoutoinvokeatable-valuedfunctionforeachrowofatableexpression.
ThisoperatorisspecifiedintheFROMCh08.
indd2511/25/129:39:26AM252MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8clauseofthecorrespondingSELECTstatementinthesamewayastheJOINoperatorisapplied.
TherearetwoformsoftheAPPLYoperator:CROSSAPPLYCCOUTERAPPLYCCTheCROSSAPPLYoperatorreturnsthoserowsfromtheinner(left)tableexpressionthatmatchrowsintheouter(right)tableexpression.
Therefore,theCROSSAPPLYoperatorislogicallythesameastheINNERJOINoperator.
TheOUTERAPPLYoperatorreturnsalltherowsfromtheinner(left)tableexpression.
(Fortherowsforwhichtherearenocorrespondingmatchesintheoutertableexpression,itcontainsNULLvaluesincolumnsoftheoutertableexpression.
)OUTERAPPLYislogicallyequivalenttoLEFTOUTERJOIN.
Examples8.
22and8.
23showhowyoucanuseAPPLY.
Example8.
22--generatefunctioncreatefunctiondbo.
fn_getjob(@empidASINT)RETURNSTABLEASRETURNSELECTjobFROMworks_onWHEREemp_no=@empidANDjobISNOTNULLANDproject_no='p1';Thefn_getjob()functioninExample8.
22returnsthesetofrowsfromtheworks_ontable.
Thisresultsetis"joined"inExample8.
23withthecontentoftheemployeetable.
Example8.
23--useCROSSAPPLYSELECTE.
emp_no,emp_fname,emp_lname,jobFROMemployeeasECROSSAPPLYdbo.
fn_getjob(E.
emp_no)ASA--useOUTERAPPLYSELECTE.
emp_no,emp_fname,emp_lname,jobFROMemployeeasEOUTERAPPLYdbo.
fn_getjob(E.
emp_no)ASACh08.
indd2521/25/129:39:26AMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8Chapter8:StoredProceduresandUser-DefinedFunctions253Theresultisemp_noemp_fnameemp_lnamejob10102AnnJonesAnalyst29346JamesJamesClerk9031ElsaBertoniManager28559SybillMoserNULLemp_noemp_fnameemp_lnamejob25348MatthewSmithNULL10102AnnJonesAnalyst18316JohnBarrimoreNULL29346JamesJamesClerk9031ElsaBertoniManager2581ElkeHanselNULL28559SybillMoserNULLInthefirstqueryofExample8.
23,theresultsetofthetable-valuedfunctionfn_getjob()is"joined"withthecontentoftheemployeetableusingtheCROSSAPPLYoperator.
fn_getjob()actsastherightinput,andtheemployeetableactsastheleftinput.
Therightinputisevaluatedforeachrowfromtheleftinput,andtherowsproducedarecombinedforthefinaloutput.
Thesecondqueryissimilartothefirstone,butusesOUTERAPPLY,whichcorrespondstotheouterjoinoperationoftwotables.
Table-ValuedParametersInallversionsprevioustoSQLServer2008,itwasdifficulttosendmanyparameterstoaroutine.
Inthatcaseyouhadtouseatemporarytable,insertthevaluesintoit,andthencalltheroutine.
SinceSQLServer2008,youcanusetable-valuedparameterstosimplifythistask.
Theseparametersareusedtodeliveraresultsettothecorrespondingroutine.
Example8.
24showstheuseofatable-valuedparameter.
Example8.
24USEsample;GOCREATETYPEdepartmentTypeASTABLECh08.
indd2531/25/129:39:26AM254MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8(dept_noCHAR(4),dept_nameCHAR(25),locationCHAR(30));GOCREATETABLE#dallasTable(dept_noCHAR(4),dept_nameCHAR(25),locationCHAR(30));GOCREATEPROCEDUREinsertProc@DallasdepartmentTypeREADONLYASSETNOCOUNTONINSERTINTO#dallasTable(dept_no,dept_name,location)SELECT*FROM@DallasGODECLARE@DallasASdepartmentType;INSERTINTO@Dallas(dept_no,dept_name,location)SELECT*FROMdepartmentWHERElocation='Dallas'EXECinsertProc@Dallas;Example8.
24firstdefinesthetypecalleddepartmentTypeasatable.
ThismeansthatitstypeistheTABLEdatatype,sorowscanbeinsertedinit.
IntheinsertProcprocedure,the@Dallasvariable,whichisofthedepartmentTypetype,isspecified.
(TheREADONLYclausespecifiesthatthecontentofthetablevariablecannotbemodified.
)Inthesubsequentbatch,dataisaddedtothetablevariable,andafterthattheprocedureisexecuted.
Theprocedure,whenexecuted,insertsrowsfromthetablevariableintothetemporarytable#dallasTable.
Thecontentofthetemporarytableisasfollows:dept_nodept_namelocationd1ResearchDallasd3MarketingDallasTheuseoftable-valuedparametersgivesyouthefollowingbenefits:Itsimplifiestheprogrammingmodelinrelationtoroutines.
CCItreducestheroundtripstotheserver.
CCTheresultingtablecanhavedifferentnumbersofrows.
CCCh08.
indd2541/25/129:39:26AMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8Chapter8:StoredProceduresandUser-DefinedFunctions255ChangingtheStructureofUDFsTheTransact-SQLlanguagealsosupportstheALTERFUNCTIONstatement,whichmodifiesthestructureofaUDF.
Thisstatementisusuallyusedtoremovetheschemabinding.
AlloptionsoftheALTERFUNCTIONstatementcorrespondtotheoptionswiththesamenameintheCREATEFUNCTIONstatement.
AUDFisremovedusingtheDROPFUNCTIONstatement.
Onlytheownerofthefunction(orthemembersofthedb_ownerandsysadminfixeddatabaseroles)canremovethefunction.
User-DefinedFunctionsandCLRThediscussionin"StoredProceduresandCLR"earlierinthechapterisalsovalidforUDFs.
TheonlydifferenceisthatyouusetheCREATEFUNCTIONstatement(insteadofCREATEPROCEDURE)tostoreaUDFasadatabaseobject.
Also,UDFsareusedinadifferentcontextfromthatofstoredprocedures,becauseUDFsalwayshaveareturnvalue.
Example8.
25showstheC#programusedtodemonstratehowUDFsarecompiledanddeployed.
Example8.
25usingSystem;usingSystem.
Data.
Sql;usingSystem.
Data.
SqlTypes;publicclassbudgetPercent{privateconstfloatpercent=10;publicstaticSqlDoublecomputeBudget(floatbudget){floatbudgetNew;budgetNew=budget*percent;returnbudgetNew;}};TheC#sourceprograminExample8.
25showsaUDFthatcalculatesthenewbudgetofaprojectusingtheoldbudgetandthepercentageincrease.
(ThedescriptionoftheC#programisomittedbecausethisprogramisanalogtotheprograminExample8.
13.
)Example8.
26showstheCREATEASSEMBLYstatement,whichisnecessaryifyouwanttocreateadatabaseobject.
Ch08.
indd2551/25/129:39:26AM256MicrosoftSQLServer2012:ABeginner'sGuideSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8Example8.
26USEsample;GOCREATEASSEMBLYcomputeBudgetFROM'C:\computeBudget.
dll'WITHPERMISSION_SET=SAFETheCREATEFUNCTIONstatementinExample8.
27storesthecomputeBudgetassemblyasthedatabaseobject,whichcanbeusedsubsequentlyindatamanipulationstatements,suchasSELECT,asshowninExample8.
28.
Example8.
27USEsample;GOCREATEFUNCTIONReturncomputeBudget(@budgetReal)RETURNSFLOATASEXTERNALNAMEcomputeBudget.
budgetPercent.
computeBudgetExample8.
28USEsample;SELECTdbo.
ReturncomputeBudget(321.
50)Theresultis3215.
NoteYoucaninvokeanexistingUDFatseveralplacesinsideaSELECTstatement.
Example8.
19showsitsusewiththeWHEREclause,Example8.
21intheFROMclause,andExample8.
28intheSELECTlist.
SummaryAstoredprocedureisaspecialkindofbatch,writteneitherintheTransact-SQLlanguageorusingtheCommonLanguageRuntime(CLR).
Storedproceduresareusedforthefollowingpurposes:TocontrolaccessauthorizationCCTocreateanaudittrailofactivitiesindatabasetablesCCCh08.
indd2561/25/129:39:26AMSQL_2008/MicrosoftSQLServer2012:ABG/Petkovic/176160-8/Chapter8Chapter8:StoredProceduresandUser-DefinedFunctions257ToenforceconsistencyandbusinessruleswithrespecttodatamodificationCCToimprovetheperformanceofrepetitivetasksCCUser-definedfunctionshavealotincommonwithstoredprocedures.
ThemaindifferenceisthatUDFsdonotsupportparametersbutreturnasingledatavalue,whichcanalsobeatable.
MicrosoftsuggestsusingTransact-SQLasthedefaultlanguageforcreatingserver-sideobjects.
(CLRisrecommendedasanalternativeonlywhenyourprogramcontainsalotofcomputation.
)ThenextchapterdiscussesthesystemcatalogoftheDatabaseEngine.
ExercisesE.
8.
1Createabatchthatinserts3000rowsintheemployeetable.
Thevaluesoftheemp_nocolumnshouldbeuniqueandbetween1and3000.
Allvaluesofthecolumnsemp_lname,emp_fname,anddept_noshouldbesetto'Jane','Smith',and'd1',respectively.
E.
8.
2ModifythebatchfromE.
8.
1sothatthevaluesoftheemp_nocolumnshouldbegeneratedrandomlyusingtheRANDfunction.
(Hint:UsethetemporalsystemfunctionsDATEPARTandGETDATEtogeneratetherandomvalues.
)Ch08.
indd2571/25/129:39:26AM