Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .Net - P3

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
57
lượt xem
14
download

Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .Net - P3

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'programming microsoft sql server 2000 with microsoft visual basic .net - p3', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:
Lưu

Nội dung Text: Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .Net - P3

  1. DROP VIEW vewEmailContacts GO --Create view to select all columns for --all rows from the EmailContacts table. CREATE VIEW vewEmailContacts AS SELECT * FROM EmailContacts GO --Select all columns for all rows from --the vewEmailContacts view. SELECT * FROM vewEmailContacts Con t r a st ing Un e n cr ypt e d a n d Encr ypt e d Vie w s Wit h m inor ext ensions, t he preceding sam ple can serv e as a t em plat e for t he creat ion of any v iew. The following script illust rat es one of t hese ext ensions. I t creat es a view in t he Chapt er04 dat abase t hat has t he Shippers t able in t he Nort hwind dat abase as it s base t able. While t he row source for a v iew can reside in anot her dat abase, t he CREATE VI EW st at em ent can creat e a v iew only in t he curr ent dat abase. Sim ilarly, t he DROP VI EW st at em ent can r em ove a view only from t he cur rent dat abase. An easy way t o r efer ence a row source fr om anot her SQL Serv er dat abase is t o use a t hree- part nam e. The first part refers t o t he alt ernat e dat abase nam e, Nort hwind in t his case. The second part designat es t he owner of t he obj ect prov iding t he row source. When t he row source ow ner is t he default dbo user, you can om it it s ex plicit designat ion ( as in t he follow ing script ) . The t hird nam e part denot es t he nam e of t he dat abase obj ect prov iding t he r ow source for a view. Figure 4- 1 shows t he r esult set from t he SELECT st at em ent based on t he vew Shippers v iew. Not ice t hat it m at ches t he values in t he Nort hwind..Shippers t able, which is t he source for t he v ew Shippers v iew. Not ice t hat unlik e t he fir st code sam ple, t his one doesn’t include a specific reference t o t he Chapt er04 dat abase. That ’s because Query Analyzer w ill cont inue t o use Chapt er04 unt il y ou specify a different dat abase wit h a new USE st at em ent . --CreatevewShippers --Search for, and remove if found, the --vewShippers view in the Chapter04 database. IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = ’vewShippers’) DROP VIEW vewShippers GO --Create a new version of the vewShippers --view in the Chapter04 database from the --Shippers table in the Northwind database. CREATE VIEW vewShippers AS SELECT * FROM Northwind..Shippers GO --Select all rows and columns from the --vewShippers view in Chapter04. SELECT * FROM vewShippers Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  2. Figu re 4 - 1 . Th e r e su lt se t from a vie w ba se d on t h e Sh ip pe rs t a ble in t h e N or t h w in d da t ab ase . The ENCRYPTI ON at t r ibut e isn’t set by default . Set t ing encrypt ion doesn’t change t he r esult set from a SELECT st at em ent . I nst ead, it encodes t he T- SQL for a view’s definit ion. You can v erify t his by t r y ing t o display t he script for a view. The VI EW_DEFI NI TI ON colum n for t he I NFORMATI ON_SCHEMA.VI EWS v iew r et urns t he script for a v iew on each of it s r ows. The follow ing scr ipt dem onst rat es t he synt ax for inv ok ing t he ENCRYPTI ON at t ribut e. The script also dem onst rat es t he sy nt ax for ret urning t he script t hat defines a view . This script includes all com m ent s as well as t he operat ional T- SQL st at em ent s for creat ing t he v iew; t hese st at em ent s include t he CREATE VI EW st at em ent for generat ing a new v iew and t he SELECT st at em ent for defining a view’s result set . I n t his case, t he SELECT st at em ent is ident ical t o t he one in t he preceding v iew. How ev er, t he CREATE VI EW st at em ent includes t he WI TH ENCRYPTI ON clause t hat encodes t he T- SQL for t he v iew. Aft er cr eat ing t he v iew, t he script perfor m s a sim ple SELECT quer y t o v er ify t he cont ent s of t he view ’s result set . The final port ion of t he script creat es anot her r esult set w it h t he definit ion for each user- defined v iew in t he curr ent dat abase, w hich is Chapt er04 in t he sam ple. Om it t ing all rows beginning w it h “sys” for t heir TABLE_NAME colum n value in t he I NFORMATI ON_SCHEMA.VI EWS v iew excludes all sy st em views from t he final result set . --CreatevewShippersEncrypted --Search for, and remove if found, the --vewShippersEncrypted view in the Chapter04 database. IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = ’vewShippersEncrypted’) DROP VIEW vewShippersEncrypted GO --Create a new version of the vewShippersEncrypted --view in the Chapter04 database from the --Shippers table in the Northwind database. CREATE VIEW vewShippersEncrypted WITH ENCRYPTION AS SELECT * FROM Northwind..Shippers GO --Select all rows and columns from the --vewShippersEncrypted view in Chapter04. SELECT * FROM vewShippersEncrypted --List user-defined view names in Chapter04 database --along with their scripts. SELECT TABLE_NAME, VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE LEFT(TABLE_NAME,3) ’sys’ Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  3. Figur e 4- 2 shows an ex cerpt from t he result set s for t he pr eceding script s. This excerpt is from t he Result s pane of Query Analy zer w it h a Result s To Grids set t ing. The t op r esult set shows t he sam e t hree rows as in Figure 4- 1. This confirm s t hat encr ypt ing a v iew doesn’t alt er t he result fr om it s SELECT st at em ent . The second result set in Figur e 4- 2 displays t he nam es of t he t hree views creat ed t o t his point in t he chapt er . Next t o each v iew nam e is t he beginning of t he script for t he v iew. Because t he script s st art w it h com m ent s, t he VI EW_DEFI NI TI ON colum n values st art w it h t hese com m ent s. Wit h a Result s To Text set t ing for t he Result s pane, y ou can exam ine t he w hole script for each v iew except v ew ShippersEncr ypt ed. The WI TH ENCRYPTI ON clause in t he CREATE VI EW st at em ent for t his view secur es it s script so t hat t he VI EW_DEFI NI TI ON colum n of t he I NFORMATI ON_SCHEMA.VI EWS v iew cannot expose t he T- SQL t hat generat es t he view . Figu re 4 - 2 . An e xce r pt sh ow in g t h e re su lt set fr om a n e n cr yp t e d vie w a s w e ll as t h e VI EW _ D EFI N I TI ON colu m n va lu es fr om t h e I N FORM ATI ON _ SCH EM A.V I EW S vie w for t h re e vie w s in a d at a ba se . Sor t in g a n d Gr oupin g W it h in a V ie w The SELECT st at em ent t hat defines a v iew has generally t he sam e synt ax as t hat wit hin a st and- alone script . For exam ple, gr ouping rows t o aggr egat e a colum n value w or ks t he sam e in bot h st and- alone script s and t hose inside v iew s. Sim ilar ly , t he I N keyword in a WHERE clause w ork s t he sam e as well. I n cont rast , t he ORDER BY clause in a SELECT st at em ent requir es slight ly different sy nt ax inside a view t han it does out side a view . I n part icular, ORDER BY inside a v iew requir es t he TOP pr edicat e aft er t he SELECT k eyw ord. The TOP predicat e, in t urn, r equires an argum ent t o designat e how m any r ecords t o ret ur n. I f you want all t he rows from a source, follow TOP w it h 100 PERCENT. You can designat e any ot her percent age as w ell as a num ber for any num ber of rows. Trailing TOP w it h t he num ber 10 w it hout t he PERCENT k eyw ord ret ur ns t he first 10 rows in t he r esult set . When you use an ORDER BY clause, t hose r ows will be t he highest or low est colum n values on a sort dim ension depending on t he sort order . The synt ax for designat ing a sort order in an ORDER BY clause is t he sam e in a SELECT st at em ent in or out of a v iew. The follow ing scr ipt shows t he cr eat ion and r et urn of values from a v iew t hat groups and sort s colum n values. The SELECT st at em ent for t he v iew also includes a crit er ion t hat filt ers ex clusively for count r ies beginning wit h t he let t er B or C. Chapt er 3 included a sim ilar st and- alone script for count ing t he num ber of cust om ers by cit y w it hin count r y. The SELECT st at em ent in t he follow ing script is dist inct because of it s use of t he TOP pr edicat e. While t he TOP predicat e w ill w or k in a st and- alone scr ipt , it isn’t necessary. --CreatevewCustomersInCountryCity --Search for, and remove if found, the --vewCustomersInCountryCity view in the Chapter04 database. IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = ’vewCustomersInCountryCity’) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  4. DROP VIEW vewCustomersInCountryCity GO --Create a new version of the vewCustomersInCountryCity --view in the Chapter04 database. --To use ORDER BY clause in view you need TOP predicate --with modifier of 100 PERCENT. CREATE VIEW vewCustomersInCountryCity AS SELECT TOP 100 PERCENT Country, City, Count(CustomerID) ’# of Customers’ FROM Northwind..Customers WHERE LEFT(Country,1) IN (‘B’,’C’) GROUP BY Country, City ORDER BY Country, City GO --Select all rows and columns from the --vewCustomersInCountryCity view in Chapter04. SELECT * FROM vewCustomersInCountryCity Vie w s for Re m ot e a n d H e t er oge n e ou s Sou r ce s I t is oft en necessary t o view dat a r esiding on anot her SQL Serv er inst ance or ev en in anot her t ype of dat abase form at . T- SQL prov ides sev er al appr oaches t o sat isfying t hese k inds of r equirem ent s. The OPENROWSET funct ion is a flex ible approach because it can accom m odat e ad hoc quer ies as w ell as t hose perform ed on a regular basis. As m ent ioned prev iously, Books Online r ecom m ends t hat y ou use link ed ser vers w hen it is necessary t o query a r em ot e or het er ogeneous source on a regular basis. Howev er, you can inv ok e t he OPENROWSET funct ion for a user id t hat doesn’t hav e m em bership in t he sysadm in or set upadm in fix ed serv er r oles. The OPENROWSET funct ion depends only on t he perm issions for t he user id passed t o t he ot her dat a source. This sect ion present s a ser ies of OPENROWSET sam ples designed t o help you underst and r em ot e dat a access. Cr e a t in g a Vie w f or An ot he r SQL Se r ve r I n st a n ce One t ypical requir em ent is t o v iew a SQL Serv er row source, such as a t able, on anot her serv er. You can use t he OPENROWSET funct ion t o perform t his t ask, wit h argum ent s t hat specify a prov ider, ot her elem ent s of a connect ion st ring, and a SELECT st at em ent . The OPENROWSET funct ion can serv e as an argum ent for t he FROM clause of a SELECT st at em ent . This out er SELECT st at em ent , in t urn, m ust reside in a CREATE VI EW st at em ent w hen y our goal is t o cr eat e a v iew in t he curr ent dat abase t hat exposes a row source in anot her dat abase. When t he inner SELECT st at em ent — t he one in t he call t o t he OPENROWSET funct ion— point s at anot her SQL Serv er inst ance, t he prov ider for t he funct ion should be SQLOLEDB. Next y ou can denot e t he rem aining elem ent s of t he connect ion st r ing for t he ot her ser ver in t he follow ing order : t he ser ver inst ance nam e, a SQL Serv er login for t he serv er , and a password for t he login. Follow t he prov ider nam e by a com m a, but use a sem icolon for a delim it er aft er t he ser ver nam e and login nam e. A com m a separat es t he password fr om t he SELECT st at em ent . The follow ing scr ipt creat es a v iew on one SQL Ser ver running SQL Server 2000 t hat point s at a t able on t he cabxli ser ver running t he MSDE v ersion com pat ible wit h SQL Ser ver 7. You need t w o inst ances of SQL Ser ver t o evaluat e t his script , Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  5. but y ou can nam e t he inst ances anyt hing y ou want . Just change t he r eferences t o cabxli t o t he nam e of a SQL Ser ver inst ance t o which you can connect . By t he way , t he t able is t he aut hors t able in t he pubs dat abase; MSDE doesn’t rout inely inst all w it h t he pubs dat abase. Because cabx li is an int er nal t est serv er running Windows 98, t he serv er is available w it h sa and an em pt y password. Pr oduct ion serv ers should always have a password for t he sa login if you ar en’t for cing Windows aut hent icat ion. The SELECT st at em ent r efer ences t he aut hors t able in t he pubs dat abase on t he cabx li ser ver. The ORDER BY clause along wit h t he TOP predicat e sort s t he result set by aut hor first nam e w it hin aut hor last nam e. The out er SELECT st at em ent t ak es t he OPENROWSET funct ion as t he ar gum ent for it s FROM clause. The SELECT list for t he out er SELECT st at em ent list s t he aut hors by first nam e, last nam e, and phone num ber, in t hat order. --CreatevewAuthorsSortedOnCabxli --Search for, and remove if found, the --vewAuthorsSortedOnCabxli view in the Chapter04 database. IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = ’vewAuthorsSortedOnCabxli’) DROP VIEW vewAuthorsSortedOnCabxli GO --Create a new version of the vewAuthorsSortedOnCabxli --view in the Chapter04 database from the --Shippers table in the Northwind database. CREATE VIEW vewAuthorsSortedOnCabxli AS SELECT au_fname, au_lname, phone FROM OPENROWSET(‘SQLOLEDB’,’cabxli’;’sa’;’’, ’SELECT TOP 100 PERCENT * FROM pubs..authors ORDER BY au_lname, a u_fname’) GO --Select all rows and columns from the --vewAuthorsSortedOnCabxli view in Chapter04. SELECT * FROM vewAuthorsSortedOnCabxli GO Cr e a t in g a Vie w f or a n Acce ss D a t a ba se I t isn’t uncom m on t o need t o upgrade Access applicat ions for t he use of an Access dat abase via a SQL Ser ver solut ion. While y ou can perform a full- scale upsizing, it is possible t hat t he OPENROWSET funct ion can dram at ically r educe t he effort of w ork ing w it h Access dat a from SQL Ser ver. That ’s because t he funct ion perm it s a SQL Ser ver solut ion t o v iew Access dat a wit hout t he need of t ransport ing t he dat a from Access t o SQL Serv er. Ther efore, y ou save t he conv ersion effort . I n addit ion, your client s av oid t he disrupt ion t hat could ar ise if t heir fam iliar Access solut ion wer e unavailable because y ou replaced it wit h a SQL Ser ver applicat ion. At t he sam e t im e, new applicat ions can expose dat a from t he Access dat abase. So long as you don’t expect t o exper ience bot t lenecks relat ed t o t he capacit y of t he Access dat abase, t his appr oach bears considerat ion. I n any ev ent , t he approach support s t he easy av ailabilit y of Access dat a from SQL Ser ver v iews. You can use an OPENROWSET funct ion t o connect wit h an Access dat abase m uch lik e y ou use t he funct ion t o connect w it h a SQL Ser ver dat abase on anot her SQL Ser ver inst ance. The OPENROWSET funct ion is t he argum ent for t he FROM clause of a SELECT st at em ent . When connect ing t o an Access dat abase, you m ust specify t he Jet dat a provider followed by t he pat h t o t he Access dat abase file, a login nam e, and a password. The OPENROWSET funct ion also has it s own SELECT Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  6. st at em ent t hat specifies t he r ow source in t he Access dat abase as well as any special set t ings, such as a WHERE clause. The follow ing scr ipt dem onst rat es a connect ion t o an Access dat abase file on t he current com put er. The pat h point s t o t he default inst allat ion of t he Nor t hw ind sam ple dat abase for Access 2002. The connect ion st ring specifies a login by t he adm in user w it h an em pt y password. This is norm al for an unsecur ed Access dat abase file, such as t he Access Nort hw ind sam ple. The SELECT st at em ent inside t he OPENROWSET funct ion call designat es t he r et ur n of all rows w it h a Count ry colum n value of USA. When designat ing a st ring in t his inst ance, t he norm al synt ax is t o enclose t he st ring argum ent , USA, wit h a pair of single quot at ion m arks. How ev er, w it hin t he OPENROWSET funct ion, single quot at ion m arks ar e alr eady used around t he SELECT st at em ent , so it ’s necessary t o use t w o single quot at ion m arks on each side of USA. I n t he follow ing script , t he out er SELECT st at em ent displays all t he colum ns from t he inner SELECT st at em ent . --CreatevewUSACustomersFromAccess --Search for, and remove if found, the --vewUSACustomersFromAccess view in the Chapter04 database. IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = ’vewUSACustomersFromAccess’) DROP VIEW vewUSACustomersFromAccess GO --Create a new version of the vewUSACustomersFromAccess --view in the Chapter04 database from the Customers table --in the Access Northwind database. (You should install the --Northwind sample if it isn’t already installed. Also, you --may need to change the path to Northwind.) CREATE VIEW vewUSACustomersFromAccess AS SELECT * FROM OPENROWSET( ’Microsoft.Jet.OLEDB.4.0’, ’c:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb ’; ’admin’;’’, ’SELECT * FROM Customers WHERE Country=‘‘USA’’’) GO --Select all rows and columns from the --vewUSACustomersFromAccess view in Chapter04. SELECT * FROM vewUSACustomersFromAccess GO Cr e a t in g a Vie w f or a n OD BC Row Sou r ce View ing an ODBC dat a source m ay be t he ult im at e in flex ibilit y because ODBC drivers are available for so m any different t ypes of dat abases. I n addit ion, t he MSDASQL prov ider, w hich is inst alled w it h Micr osoft Dat a Access Com ponent s, offers a st andard int erface t o ODBC dat a sources. The OPENROWSET funct ion t hr ough it s SELECT st at em ent let s your applicat ions choose a specific row source wit hin a dat a source or ev en filt er a r ow source t o der iv e a new cust om source for an applicat ion. Using t he OPENROWSET funct ion t o connect w it h a row source in an ODBC dat a source bears a st rong r esem blance t o using t he funct ion t o connect w it h SQL Ser ver and Jet r ow sour ces. The m ain differences ar e in t he connect ion st ring specificat ions. First y ou m ust designat e t he MSDASQL prov ider inst ead of t he Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  7. SQLOLEDB or Jet prov ider . Second you specify connect ion st ring elem ent s t hat are appr opr iat e for t he dat a source t o which y ou want t o connect . The follow ing scr ipt shows t he sy nt ax for an applicat ion of t he OPENROWSET funct ion wit h t he MSDASQL prov ider for an ODBC dat a source. I n fact , t he sam ple connect s t o a SQL Serv er dat a source wit h t he ODBC dr iv er, but t he general synt ax issues ar e t he sam e as for any dat a source. This sam ple requir es t wo inst ances of SQL Serv er . For exam ple, t he connect ion st r ing elem ent s point t o t he cab2000 server running a SQL Serv er dat abase. You can r eplace t he r efer ence t o cab2000 wit h t he nam e of any ot her inst ance of SQL Serv er on y our net wor k. The user id and password ar e, respect iv ely, sa and password. The inner SELECT st at em ent for t he OPENROWSET funct ion chooses all t he rows from t he Orders t able in t he Nort hw ind dat abase whose OrderDat e is in 1998. A WHERE clause and a DATEPART funct ion part icipat e in t he designat ion of an appropr iat e cr it er ion for t he SELECT st at em ent . The out er SELECT st at em ent ret ur ns all colum ns fr om t he Orders t able. --Createvew1998OrdersOnCab2000 --Search for, and remove if found, the --vew1998OrdersOnCab2000 view in the Chapter04 database. IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = ’vew1998OrdersOnCab2000’) DROP VIEW vew1998OrdersOnCab2000 GO --Create a new version of the vew1998OrdersOnCab2000 --view in the Chapter04 database from the Orders table --in the Northwind database on the Cab2000 server. CREATE VIEW vew1998OrdersOnCab2000 AS SELECT * FROM OPENROWSET(‘MSDASQL’, ’DRIVER={SQL Server};SERVER=cab2000;UID=sa;PWD=password’, ’SELECT * FROM Northwind..Orders WHERE DATEPART(yyyy, OrderDate) = 1998’) GO --Select all rows and columns from the --vew1998OrdersOnCab2000 view in Chapter04. SELECT * FROM vew1998OrdersOnCab2000 Join ing Row Sou r ce s for a Vie w The value of being able t o pr ocess rem ot e and het erogeneous dat a sources m ult iplies when y ou can j oin t wo r ow sources fr om different serv ers or different dat abases. Ther e are at least t wo approaches t o t his t ask. The first one is t o creat e a SELECT st at em ent t hat cont ains a JOI N operat or. I n t his appr oach, each side of t he j oin has it s own explicit OPENROWSET funct ion. The ot her approach is t o creat e t w o new v iews, each based on it s ow n OPENROWSET funct ion. Then y ou can creat e a new, t hird, view t hat j oins t he t wo views. Eit her appr oach em pow ers an applicat ion t o process concurrent ly r ow sour ces from differ ent dat abase serv ers in differ ent dat abase form at s! The follow ing scr ipt shows t he sy nt ax for t he fir st approach. Lik e sev er al of t he prev ious OPENROWSET funct ion sam ples, t his one r equir es t wo inst ances of SQL Ser ver. The scr ipt j oins rows fr om t he Orders t able in a SQL Ser ver dat abase wit h rows fr om t he Cust om er s t able in an Access dat abase file. The OPENROWSET funct ion declarat ions follow t he sy nt ax of pr ev ious sam ples t hat used t he funct ions separat ely as t he source for a view . This script sam ple j oins t he Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  8. Cust om ers r ows w it h t he Orders rows based on t heir Cust om erI D colum n values. An advant age of nest ing t he t wo OPENROWSET funct ions as t he argum ent for t he FROM clause of t he out er SELECT st at em ent is t hat y our applicat ion doesn’t requir e separat e v iews for each r ow source obj ect t hat get s j oined. This saves your applicat ion from opening t he v iews. --CreatevewAccessCustomersCab2000Orders --Search for, and remove if found, the --vewAccessCustomersCab2000Orders view in the Chapter04 database. IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = ’vewAccessCustomersCab2000Orders’) DROP VIEW vewAccessCustomersCab2000Orders GO --Create the vewAccessCustomersCab2000Orders view --in the Chapter04 database from the --OPENROWSET of CustomersFromAccess and --OPENROWSET of 1998OrdersOnCab2000. CREATE VIEW vewAccessCustomersCab2000Orders AS SELECT TOP 100 PERCENT c.CompanyName, c.ContactName, c.Phone, o.OrderID, LEFT(o.OrderDate, 11) ’Order Date’ FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’, ’C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb ’; ’admin’;’’, ’SELECT * FROM Customers WHERE Country=‘‘USA’’’) AS c JOIN OPENROWSET(‘MSDASQL’, ’DRIVER={SQL Server};SERVER=cab2000;UID=sa;PWD=password’, ’SELECT * FROM Northwind.dbo.Orders WHERE DATEPART(yyyy, OrderDate) = 1998’) AS o ON c.CustomerID = o.CustomerID ORDER BY c.CompanyName, o.OrderID GO --Select all rows and columns from the --vewAccessCustomersCab2000Orders view in Chapter04. SELECT * FROM vewAccessCustomersCab2000Orders The next script shows t he synt ax for t he alt er nat iv e appr oach t o j oining t wo het er ogeneous dat a sources. Again, y ou need t wo SQL Server inst ances t o r un t he sam ple. This alt er nat iv e j oins t wo pr ev iously creat ed v iews. I n t his inst ance, each v iew is fr om a pr ior sam ple in t his chapt er. I n addit ion, t he t wo v iews corr espond t o t he SELECT st at em ent s for each of t he nest ed OPENROWSET funct ions in t he pr ior sam ple. Ther efore, t he r esult is ident ical for t he next script and t he prior scr ipt . Howev er , t he code for t he next script is dram at ically sim pler. By segm ent ing t he t wo OPENROWSET funct ions int o separat e v iews, t he second approach m ak es it easier t o debug t he synt ax . On t he ot her hand, wit h t his approach your applicat ion r equir es t he addit ional ov er head of m anaging t wo separat e v iews. This includes cr eat ing, m aint aining, and opening t he v iews. --Createvew2JoinedViews --Search for, and remove if found, the --vew2JoinedViews view in the Chapter04 database. IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = ’vew2JoinedViews’) DROP VIEW vew2JoinedViews GO Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  9. --Create a new version of the vew2JoinedViews --view in the Chapter04 database from --two other previously existing views. CREATE VIEW vew2JoinedViews AS Select TOP 100 PERCENT c.CompanyName, c.ContactName, c.Phone, o.OrderID, LEFT(o.OrderDate, 11) ’Order Date’ FROM vewUSACustomersFromAccess c JOIN vew1998OrdersOnCab2000 o ON (c.CustomerID = o.CustomerID) ORDER BY c.CompanyName, o.OrderID GO --Select all rows and columns from the --vew2JoinedViews view in Chapter04. SELECT * FROM vew2JoinedViews GO I n t r odu ct ion t o St or e d Pr oce dur es St or ed pr ocedur es are com piled bat ches of T- SQL st at em ent s. The bat ch of st at em ent s can cont ain near ly all t he T- SQL st at em ent t ypes. While a st or ed procedur e can r et ur n a result set t he sam e way a v iew does, st ored pr ocedur es are m ore pow erful in several r espect s. A v iew is a v irt ual t able; a st or ed procedur e is m or e lik e a procedure in Visual Basic. You can pass it param et ers, and it can r et urn values t hrough it s result set , out put param et ers, and r et urn st at us values. I n fact , st or ed procedur es can ret ur n m ult iple r esult set s, while views ar e lim it ed t o a single r esult sim ilar t o a t able. Use s for St or e d Pr oce du r e s St or ed pr ocedur es have four m ain uses. First , t hey can r et urn one or m or e result set s. You can program a st ored procedure t o r et ur n m ult iple r esult set s as easily as including m ult iple SELECT st at em ent s w it hin a single st ored procedure. Anot her way st ored pr ocedur es can ret urn r esult set s is via out put param et ers. An out put param et er is a scalar value. A scalar value is a single value, such as a st ring or an int eger , t hat isn’t a part of a rowset . While a r esult set can cont ain a scalar value, r esult set s norm ally cont ain set s of values. Out put param et ers prov ide an efficient m eans for st ored pr ocedures t o r et ur n scalar values. St ored procedur es can also ret ur n int eger values t hat indicat e how a st or ed pr ocedur e t erm inat es. SQL Serv er docum ent at ion r efers t o t hese r et ur n values as ret ur n st at us values. When a st or ed pr ocedur e can follow any of sev eral int er nal processing pat hs, ret ur n st at us values can indicat e t o a calling r out ine which pat h a st ored pr ocedur e pursued. A second m aj or use of st or ed pr ocedur es is t he processing of input param et ers. These param et ers enable y our applicat ions t o cont rol dynam ically t he t hings t hat a st ored pr ocedur e ret urns. Not all T- SQL st at em ent s t ak e param et ers. I n t hese circum st ances, you can com bine t he use of param et ers w it h cont rol- of- flow st at em ent s, such as I F…ELSE st at em ent s, t o det erm ine what a st or ed procedure ret ur ns. One com m on use for param et ers is in t he WHERE clause of SELECT st at em ent s. By using input param et er values as crit er ion values for WHERE clause expr essions, y our applicat ions can dy nam ically cont rol a st or ed procedur e’s result set . When users set t he par am et er values, y ou enable users t o cont r ol an applicat ion dynam ically at run t im e. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  10. A t hird m aj or use for st ored procedur es is t he m anagem ent of insert / updat e/ delet e operat ions for r ow sources. I n t his cont ext , a st ored procedur e prov ides v alue t o an applicat ion w it hout r et ur ning a r esult set , a param et er value, or a r et urn st at us v alue. The procedur e sim ply m odifies a r ow source. Because st ored procedur es can set param et ers based on user input and t he procedures can use param et ers for insert / updat e/ delet e operat ions, users can cont r ol t he m odificat ions t o a r ow source at r un t im e. Fourt h, y ou w ill lear n how t o use st or ed procedur es as pr ogram s im plem ent ed wit h a bat ch of T- SQL st at em ent s. This fourt h use under lies and ext ends t he ot her t hree uses for st ored pr ocedures. These st at em ent s can include SELECT st at em ent s, ot her st at em ent s for insert / updat e/ delet e operat ions, and cont r ol- of- flow st at em ent s, such as I F…ELSE st at em ent s. I n addit ion, you can specify any of four t ypes of values— local variables, global var iables, param et ers, and ret ur n st at us values— t o cont rol t he dy nam ic behav ior of a st or ed pr ocedur e and how it com m unicat es wit h it s calling procedure. N ot e See t he “Cont rol- of- Flow” t opic in Book s Online for a good st ar t ing point t hat helps you t o learn about t radit ional program m ing t echniques for st or ed procedures. Anot her especially useful Books Online t opic for learning about st ored procedure pr ogr am m ing is “Pr ogram m ing St ored Procedures.” Re u sin g T- SQL St a t e m e n t s w it h St or e d Pr oce du r e s One of t he m aj or advant ages of st ored pr ocedures is t hat t hey can package T- SQL st at em ent s for r euse. Four T- SQL st at em ent s help y ou m anage t hese blocks of code. Two st at em ent s, CREATE PROCEDURE and ALTER PROCEDURE, enable t he definit ion and refinem ent of t he code wit hin a st or ed pr ocedur e. Wit h t he DROP PROCEDURE st at em ent , you can rem ov e a st or ed pr ocedur e fr om a dat abase. The EXECUTE st at em ent perm it s you t o run a st ored pr ocedure. The CREATE PROCEDURE st at em ent let s you creat e a st or ed procedur e. You can abbrev iat e t his st at em ent as CREATE PROC. Follow t he st at em ent nam e wit h t he nam e for your st or ed pr ocedur e. SQL Serv er has a r ich collect ion of syst em st ored procedures, which t ypically st art w it h sp_. Chapt er 2 includes ex am ples of how t o use syst em st or ed pr ocedur es w it h t ables. Syst em st ored pr ocedur es are available for m anaging ev ery aspect of SQL Ser ver perform ance and adm inist rat ion. To avoid conflict s wit h syst em st or ed procedur es, av oid st art ing your own user - defined st or ed pr ocedur es w it h t he sp_ prefix. This chapt er uses udp as a prefix for user - defined st ored pr ocedur es. Lik e view nam es, st or ed procedur es should follow t he st andard rules for SQL Serv er ident ifiers. The CREATE PROC st at em ent s t ypically hav e t hr ee or four m ain elem ent s. First , CREATE PROC declares t he st or ed pr ocedur e and assigns a nam e t o it . Second, you can specify one or m or e param et ers for t he pr ocedur e. The param et er declarat ions ar e opt ional. Third, t he AS keyword serv es as a t ransit ional word bet ween t he declarat ion elem ent s and t he T- SQL code ( t he fourt h elem ent ) t hat enables a st or ed procedur e t o perform a t ask. The follow ing t em plat e illust rat es how t o ar range t hese st or ed procedur e elem ent s. CREATE PROC procedurename Parameter specifications AS Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  11. T-SQL code Aft er y ou creat e a st ored procedure, y ou can change it s code in at least t wo different way s. First , y ou can inv ok e t he DROP PROCEDURE ( or DROP PROC) st at em ent t o rem ov e t he pr ior v ersion and t hen inv ok e a new CREATE PROC st at em ent wit h t he sam e nam e as t he rem ov ed procedure. To delet e an ex ist ing st ored procedure w it h t he DROP PROC st at em ent , sim ply follow t he keyword phrase w it h t he nam e of t he st ored procedure t hat y ou want t o r em ov e. Wit h t his approach, y ou w ipe out any perm issions assigned t o users for t he dropped st or ed procedur e. Alt ernat iv ely , y ou can inv ok e t he ALTER PROCEDURE ( or ALTER PROC) st at em ent . This allows y ou t o respecify t he param et ers and t he code w it hin a st ored procedure w hile it m aint ains any perm ission set t ings for t he st or ed procedur e t hat y ou m odify . Except for t he keyw ord declar ing it , t he ALTER PROC st at em ent has t he sam e form at as t he CREATE PROC st at em ent . Your applicat ions can use t he EXECUTE ( or EXEC) st at em ent t o inv ok e a st ored procedur e init ially cr eat ed w it h a CREATE PROC st at em ent . I n it s m ost basic represent at ion, follow t he EXEC k eyw ord w it h t he nam e of t he st ored procedur e t hat y ou want t o r un. The synt ax for t he EXEC st at em ent perm it s y ou t o assign values for input param et ers as well as accept out put param et er and ret ur n st at us values. I n addit ion, t he EXEC st at em ent can also r et ur n one or m ore result set s— depending on t he T- SQL code t hat populat es t he st ored procedure. This chapt er includes num erous sam ples t hat illust rat e t he synt ax for inv ok ing st or ed procedur es w it h t he EXEC st at em ent . Usin g Pa r a m e t e r s, Loca l Va r ia bles, a n d Globa l V a r ia ble s Alt hough param et ers, local variables, and global var iables can, of course, be used elsewhere, using t hem wit h st or ed procedures especially enhances t he value of t he procedures in an applicat ion. Ther e are t wo basic kinds of param et ers— input param et ers and out put param et ers. Param et er nam es m ust begin w it h t he @ sym bol. The r em ainder of a param et er’s nam e m ust follow t he st andar d SQL Ser ver ident ifier convent ions. Param et ers have dat a t ypes t hat correspond t o t hose for t able colum n v alues. ( See Chapt er 3. ) I nput param et ers perm it y ou t o cust om ize t he operat ion of a st or ed procedur e at run t im e. For exam ple, you can use input param et ers t o specify t he colum n values for a st ored procedure t hat adds a new r ow t o a row source. The CREATE PROC and ALTER PROC st at em ent s perm it y ou t o assign default values for input param et ers. These default values allow a st ored procedure t o use a param et er wit hout t est ing for a null v alue ev en if t he user om it s t he specificat ion of a param et er when inv ok ing t he st or ed procedur e. Out put param et ers repr esent values developed from w it hin a st or ed pr ocedur e. These can be values com put ed by t he pr ocedur e or SQL Serv er . A st ored procedur e can pass back as an out put param et er t he I DENTI TY value for a new row in a t able so t hat anot her st ored pr ocedur e can use t he out put par am et er as a foreign k ey value for a new row in a relat ed t able. I n t his scenar io, t he out put param et er value from one st or ed pr ocedur e ser ves as t he input param et er value for a second one. A local var iable is a m em or y v ar iable t hat you assign for use inside a st or ed procedur e. Use t he DECLARE keyword for designat ing local var iables and t he SET keyw ord for assigning v alues t o a local var iable. You can also assign a v alue t o a local v ar iable w it h a SELECT st at em ent t hat ret urns a scalar value, such as t he count of t he num ber of rows in a t able. The scope of a local v ariable is t he st ored procedur e t hat declares t he var iable. Like param et ers, local v ariable ident ifiers m ust begin wit h t he @ sym bol. The rem ainder of t he local v ariable nam e m ust follow st andard SQL Serv er ident ifier Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  12. conv ent ions. The DECLARE st at em ent for a local var iable m ust include a dat a t ype for t he var iable. You can use any dat a t ype except for t ext , nt ex t , and im age. A local var iable’s dat a t ype specificat ion det erm ines t he t ype of cont ent t hat t he var iable can hold. Local var iables can be used in expr essions and as argum ent s for cont r ol- of- flow st at em ent s t o cont r ol t he operat ion of a st ored procedur e. Local var iables can w or k in coordinat ion wit h param et ers by accept ing values fr om param et ers and passing values t o t hem . Dev elopers fam iliar w it h SQL Serv er v ersions pr ior t o 7.0 m ay be fam iliar wit h t he t erm global var iables. SQL Ser ver 2000 r efers t o t hese global var iables as funct ions. A global var iable funct ion nam e st art s wit h @@. These global var iable funct ions ret ur n values t o st or ed pr ocedur es t hat cont ain syst em infor m at ion. You can display t he full list of 33 @@ var iable funct ions from t he I ndex t ab in Books Online by ent er ing @@ as t he keyword. This chapt er illust rat es t he use of t he @@ROWCOUNT funct ion, which ret ur ns t he num ber of r ows affect ed by t he last T- SQL st at em ent . Ot her @@ funct ions t hat I r egular ly find part icular ly conv enient include @@I DENTI TY, @@ERROR, and @@DBTS. These t hr ee funct ions ret ur n t he last I DENTI TY value insert ed, t he er ror num ber associat ed wit h t he last T- SQL st at em ent , and t he cur rent t im est am p value w it hin a dat abase. Cr e a t in g a n d Usin g St or e d Pr ocedu r e s The purpose of t his sect ion is t o int roduce you t o synt ax for creat ing and using st ored procedures. This sect ion shows you t ypical ways of apply ing t he CREATE PROC st at em ent . I n addit ion, you learn com m on ways of specify ing t he EXEC st at em ent t o run a st or ed pr ocedur e. The sect ion illust rat es t echniques for designat ing input param et ers when y ou cr eat e a st ored pr ocedur e as w ell as way s of specify ing input param et er values when you r un a st or ed procedur e. D yna m ica lly Se le ct in g f r om a Row Sou r ce One of t he m ain advant ages of st or ed pr ocedur es com par ed w it h v iews is t hat st ored procedures perm it t he use of param et ers. Bot h views and st or ed procedur es can inv ok e SELECT st at em ent s. Howev er , st or ed procedur es let y ou assign values t o param et ers in WHERE clause expressions at r un t im e. This capabilit y m eans y our applicat ions can t ak e input from users t o designat e w hich rows a st ored pr ocedur e ret urns in it s result set . Wit h v iews, you would hav e t o preprogram a differ ent view for each set of r ow s you want ed. The follow ing scr ipt has t hr ee bat ches of T- SQL code. The first bat ch r em ov es any prior v ersion of t he udpList ShippersRow in t he curr ent dat abase. The first bat ch uses t he I NFORMATI ON_SCHEMA.ROUTI NES view t o search for an ex ist ing st ored procedur e wit h t he nam e udpList ShippersRow. I f one alr eady exist s wit h t hat nam e, t he bat ch invokes t he DROP PROCEDURE st at em ent t o rem ov e it . The second bat ch inv ok es t he CREATE PROC st at em ent t o creat e a new st or ed procedur e nam ed udpList ShippersRow. This procedur e t ak es a single param et er nam ed @RowI D w it h an int dat a t ype. The pr ocedur e uses t he param et er t o specify t he ShipperI D colum n value for t he r ow it ret urns; see t he WHERE clause for t he sy nt ax of how t o do t his. The basic SELECT st at em ent ret urns all t he colum ns fr om t he Shippers t able in t he Nort hwind dat abase. You can t ell from t he synt ax t hat t his is t he SQL Ser ver v ersion of t he dat abase. ( Not ice t he FROM clause argum ent .) All t he rem aining st ored pr ocedur e sam ples use j ust SQL Ser ver dat abases. The final bat ch consist s of a single EXEC st at em ent . The st at em ent r uns t he st ored procedure creat ed in t he pr ev ious bat ch and designat es a v alue for t he Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  13. RowI D par am et er . Failing t o specify a RowI D param et er value causes t he procedur e t o fail wit h an er ror m essage. Designat ing a nonex ist ent ShipperI D colum n value w it h RowI D produces an em pt y result set . On t he ot her hand, specify ing any of t he ex ist ing ShipperI D colum n values causes t he procedure t o generat e a r esult set w it h all t he colum ns for t hat row in t he Shippers t able. --CreateudpListShippersRow --Delete previous version of udpListShippersRow --stored procedure if it exists. IF EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ’PROCEDURE’ AND ROUTINE_NAME = ’udpListShippersRow’) DROP PROCEDURE udpListShippersRow GO --Create udpListShippersRow with an --input parameter to specify a row. CREATE PROC udpListShippersRow @RowID int AS SELECT * FROM Northwind..Shippers WHERE ShipperID = @RowID GO --Run udpListShippersRow with an --input parameter of 2. EXEC udpListShippersRow 2 Re t u r n ing a Sor t ed Re su lt Se t Ev en a basic SELECT st at em ent can y ield benefit s when it is m ade available from a st ored pr ocedur e. For exam ple, t he use of t he ORDER BY clause in a v iew requir es t he concurr ent use of t he TOP predicat e. While t his is cert ainly not com plicat ed, it is j ust one m or e t hing you hav e t o rem em ber t o get r ight . The synt ax for using t he ORDER BY clause in a st or ed pr ocedur e is j ust like t hat in a st and- alone T- SQL script . I n ot her w ords, you don’t need a TOP pr edicat e for your SELECT st at em ent . The follow ing scr ipt shows t he ORDER BY clause w it hin a SELECT st at em ent t hat det erm ines t he result set fr om a st ored pr ocedure. The SELECT st at em ent generat es a result set based on t he Shippers t able, w it h t he r ows sort ed by Com panyNam e colum n values. This r et urns t he rows in a different order t han t he default one based on t he ShipperI D colum n values. The scr ipt again relies on a t hr ee- part st rat egy. The first part rem ov es an old version of t he udpShippersSort edBy Com panyNam e st ored procedur e. The second part inv ok es t he CREATE PROC st at em ent t o add t he new st or ed procedur e. The t hird part runs t he new ly cr eat ed st ored procedure w it h t he EXEC st at em ent . Because t his st ored procedure doesn’t t ake any param et ers, you can j ust follow t he EXEC keyw ord wit h t he nam e of t he st or ed pr ocedur e. There is no need for anyt hing else aft er t he EXEC k ey word. --CreateudpShippersSortedByCompanyName --Delete previous version of udpShippersSortedByCompanyName --stored procedure if it exists. IF EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ’PROCEDURE’ AND ROUTINE_NAME = ’udpShippersSortedByCompanyName’) DROP PROCEDURE udpShippersSortedByCompanyName Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  14. GO --Create udpShippersSortedByCompanyName with an --input parameter to specify a row. CREATE PROC udpShippersSortedByCompanyName AS SELECT * FROM Northwind..Shippers ORDER BY CompanyName GO --Run udpShippersSortedByCompanyName. EXEC udpShippersSortedByCompanyName GO Re t u r n ing t h e Scr ipt f or a V ie w St or ed pr ocedur es are an ext r em ely flex ible t ool. You can use SELECT st at em ent s in t he full range of cases t hat use v iews and st and- alone T- SQL st at em ent s. For exam ple, y ou can quer y I NFORMATI ON_SCHEMA views t o uncov er infor m at ion about t he obj ect s in a dat abase. An advant age of a st or ed pr ocedur e is t hat t he T- SQL it cont ains is com piled. A st and- alone T- SQL st at em ent m ust be com piled befor e SQL Server can use it . Therefore, t he st or ed procedur e can r un t he sam e T- SQL code fast er. N ot e The sp_execut esql sy st em st ored procedur e offers som e of t he benefit s of st ored procedur es for st and- alone T- SQL SELECT st at em ent s. The follow ing scr ipt dem onst rat es t he use of a st ored pr ocedur e t o quer y t he I NFORMATI ON_SCHEMA.VI EWS v iew. The r esult set for t his v iew cont ains a r ow for each v iew in t he cur rent dat abase. The v iew ’s VI EW_DEFI NI TI ON colum n ret ur ns t he T- SQL script defining a v iew. The TABLE_NAME colum n r et ur ns t he nam e for a v iew. The st or ed procedure accept s a param et er t hat designat es a v iew ’s nam e. The st ored procedure’s SELECT st at em ent passes t he T- SQL scr ipt for a v iew t o a local variable, @st rDefinit ion. The local variable accept s t he value in t he VI EW_DEFI NI TI ON colum n value for t he row wit h a TABLE_NAME colum n value equal t o t he param et er passed t o t he st or ed pr ocedur e. Then a PRI NT st at em ent displays t he cont ent s of t he local var iable in t he Messages pane. The st or ed procedure’s approach works for v iews wit h up t o 8000 charact ers fr om t he default code page for t he com put er on w hich y ou dev eloped t he st ored procedur e. This is because t he varchar dat a t y pe for t he @st rDefinit ion local variable has a m ax im um lengt h of 8000 charact ers in t he default code page for a com put er. I f y ou expect your v iew script s t o have m or e charact ers or y our applicat ion r uns on com put ers using m ult iple code pages, you need anot her approach for st or ing t he view’s T- SQL script . For exam ple, y ou can use an out put param et er inst ead of a local var iable. Assign a t ext or an nt ext dat a t ype t o t he param et er. When using t he out put param et er approach, y ou can pr int t he script in t he calling rout ine for t he st or ed procedur e. Recall t hat a t ext dat a t ype can hold up t o 2 31 - 1 charact ers, and a dat a t ype value can hold up t o 2 30 - 1 charact ers. Users can alt er t he r et urn value t hat appears in t he Messages pane by changing t he nam e of t he v iew passed t o t he st or ed procedur e. The EXEC st at em ent t o inv oke t he st ored pr ocedur e encloses t he param et er in single quot at ion m arks. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  15. This is because t he st or ed pr ocedur e assigns a varchar dat a t ype t o t he param et er st or ing a view’s nam e. --CreateudpScriptForView --Remove prior version of stored procedure. IF EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ’PROCEDURE’ AND ROUTINE_NAME = ’udpScriptForView’) DROP PROCEDURE udpScriptForView GO --Create stored procedure to print definition --for a view in the current database. CREATE PROC udpScriptForView @vewName varchar(128) AS DECLARE @strDefinition varchar(8000) SET @strDefinition = (SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = @vewName) PRINT @strDefinition GO --Run stored procedure and pass view name. EXEC udpScriptForView ’vewShippers’ GO Pr ocessing St or e d Pr ocedu r e Ou t pu t s One of t he t asks t hat st or ed procedur es ser ve especially w ell is get t ing dat a back t o a calling procedur e. St or ed pr ocedur es can achiev e t his goal in sev eral ways. First , t hey perm it t he t r ansfer of dat a back t o t he calling pr ocedur e in t he form of result set s. You can r et ur n m ult iple r esult set s from a single st or ed procedur e. Second, a st ored pr ocedur e can r et ur n scalar values v ia out put param et ers. Third, code calling a st ored pr ocedure can process ret ur n st at us values. I n any one applicat ion, y ou can concurr ent ly use any com binat ion of t hese t hr ee processes for ret ur ning v alues. This sect ion elaborat es on t hem and dem onst rat es t he synt ax for im plem ent ing each. Re t u r n ing Tw o Re sult Se t s fr om a St or e d Pr oce du r e I t ’s sim ple t o r et ur n m ult iple r esult set s fr om a single st ored pr ocedur e: j ust include a separat e SELECT st at em ent for each r esult set t hat y ou want a st ored procedur e t o ret urn. I n cont rast , v iews can hav e only a single SELECT st at em ent . Once y ou st art using m ult iple SELECT st at em ent s in a st or ed pr ocedur e, you’ll find t hat it has consider ably m ore flex ibilit y t han ret ur ning rows from a t able or view. The follow ing scr ipt creat es a st ored pr ocedur e wit h t w o r esult set s. The first result set cont ains a r ow w it h t he nam e and cr eat ion dat e for each user- defined st ored procedure in a dat abase. Recall t hat t he dat abase cont ext for t hese sam ples is Chapt er04. ( You can set t he cont ext wit h a USE st at em ent .) To ret urn j ust t he user - defined st or ed procedur es from t he I NFORMATI ON_SCHEMA.ROUTI NES v iew, you need t wo cr it er ia expr essions. One expression select s j ust r ows w it h a ROUTI NE_TYPE colum n value of PROCEDURE. This expression filt ers out any user - defined funct ions. The second expr ession Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  16. rem ov es any rows w it h a ROUTI NE_NAME colum n value t hat begins w it h dt _. Because SQL Serv er uses dt _ as a pr efix for t he st or ed procedures t hat it cr eat es in a dat abase, t his expr ession leav es only user - defined st ored procedures. The second SELECT st at em ent ret urns t he value of t he @@ROWCOUNT funct ion. This funct ion is always t he value of r ecords affect ed by t he last T- SQL st at em ent . I n t his case, t he last one ret ur ns t he nam es and creat ion dat es of t he user- defined st ored procedures in a dat abase, so t he second SELECT st at em ent ret ur ns t he num ber of user- defined st or ed procedur es in t he curr ent dat abase cont ext . --CreateudpReturn2ResultSets --Remove prior version of stored procedure. IF EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ’PROCEDURE’ AND ROUTINE_NAME = ’udpReturn2ResultSets’) DROP PROCEDURE udpReturn2ResultSets GO --Create stored procedure to return one result --set for listing stored procedure names and dates --and another with the count of the stored procedures. CREATE PROC udpReturn2ResultSets AS SELECT ROUTINE_NAME, CREATED FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ’PROCEDURE’ AND LEFT(ROUTINE_NAME,3) ’dt_’ ORDER BY CREATED DESC SELECT @@ROWCOUNT ’Number of stored procedures’ GO --Run stored procedure that returns two result sets. EXEC udpReturn2ResultSets GO Figur e 4- 3 shows t he out put from r unning t he udpRet urn2Result Set s st ored procedur e. ( This is t he out put from t he preceding script .) Not ice t hat t he t op result set cont ains ROUTI NE_NAME and CREATED colum n values. This result has a row for each user - defined st ored pr ocedur e. The last row includes t he nam e and creat ion dat e for t he elevent h st or ed pr ocedur e. The second r esult set cont ains a num ber t hat is t he count of t he num ber of user - defined st ored pr ocedur es— 11. Figu r e 4 - 3 . Th e re t u r n fr om a u ser - d e fin e d st or ed pr oced u r e t h a t spe cifie s t w o r esu lt se t s. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  17. Re t u r n ing On e Re su lt Se t a n d One Pa r a m e t e r Va lu e The preceding sam ple uses a SELECT st at em ent t o ret urn a scalar value, nam ely t he cur rent value for @@ROWCOUNT. By ent er ing t he @@ROWCOUNT global variable funct ion in a SELECT st at em ent , t he sam ple r et ur ns t he current value of @@ROWCOUNT in a result set . The next sam ple illust rat es how t o ret ur n t he @@ROWCOUNT value as an out put param et er from a st or ed procedure. This inv olves a special declar at ion for t he param et er inside t he st ored pr ocedur e as well as an assignm ent expression in t he EXEC st at em ent t o r et r iev e t he value for t he out put param et er. I n t he T- SQL code t hat calls t he st or ed procedur e, you need t o t ransfer t he out put param et er t o a local var iable for use locally. I n addit ion, t he EXEC st at em ent m ust explicit ly designat e t he out put param et er. The follow ing code shows t he exact synt ax for ret ur ning @@ROWCOUNT as an out put param et er . First not ice t he line im m ediat ely aft er t he CREATE PROC st at em ent : @NumberOfRows int OUTPUT This line declares t he param et er. Not ice t hat it ends wit h t he k eyw ord OUTPUT. This k eyw ord designat es t he @Num berOfRows param et er as an out put param et er. Lat er in t he st ored procedure, a SET st at em ent assigns t he curr ent value of @@ROWCOUNT t o t he @Num berOfRows param et er , lik e t his: SET @NumberOfRows = (SELECT @@ROWCOUNT) This st or ed pr ocedur e div erges from t he preceding one by ex plicit ly inv oking t he SET NOCOUNT st at em ent w it h t he value ON. This st at em ent suppresses t he aut om at ic SQL Server m essage about t he num ber of r ows affect ed, which happens t o be t he value of @@ROWCOUNT. At t he conclusion of t he st ored procedur e, t he sam ple inv ok es t he SET NOCOUNT st at em ent a second t im e w it h t he set t ing OFF. This second invocat ion of t he SET NOCOUNT st at em ent r est ores t he default behav ior of print ing t he rows affect ed by a T- SQL st at em ent . Using a param et er ret ur ned by a st ored pr ocedure also r equir es special synt ax. First y ou need a local v ariable t o accept t he out put param et er value. This is because y ou cannot w ork dir ect ly wit h t he out put param et er in t he code t hat calls t he st or ed pr ocedur e. The sam ple code declar es a local v ariable nam ed @Ret ur nedParam Value t o st or e t he out put param et er value locally. Second you need an assignm ent st at em ent . This st at em ent m ust end w it h t he OUTPUT keyw ord. I n addit ion, t he local var iable m ust be on t he r ight side of t he equal Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  18. sign, and t he out put par am et er should appear on t he left side. Third t he out put param et er ret ur ns an int dat a t ype value. How ever, t he Pr int st at em ent t hat report s t he num ber of st ored procedur es r equir es a char act er dat a t ype, nam ely varchar. Therefore, t he code applies t he CAST funct ion t o t he local variable st oring t he out put param et er v alue; t he funct ion represent s t he int eger value as a st ring. The expression for @st rFor Pr int er com bines a st r ing const ant wit h t he CAST funct ion value. The PRI NT st at em ent t akes @st rForPr int er as it s argum ent t o pr int t he num ber of st or ed pr ocedur es w it h a br ief descr ipt ive label. --CreateudpReturn1ResultSet1Parameter --Remove prior version of stored procedure. IF EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ’PROCEDURE’ AND ROUTINE_NAME = ’udpReturn1ResultSet1Parameter’) DROP PROCEDURE udpReturn1ResultSet1Parameter GO --Create stored procedure to return one result --set for listing stored procedure names and dates along --with another containing the count of the stored procedures. CREATE PROC udpReturn1ResultSet1Parameter @NumberOfRows int OUTPUT AS SET NOCOUNT ON SELECT ROUTINE_NAME, CREATED FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ’PROCEDURE’ AND LEFT(ROUTINE_NAME,3) ’dt_’ ORDER BY CREATED DESC SET @NumberOfRows = (SELECT @@ROWCOUNT) SET NOCOUNT OFF GO --Run stored procedure that returns two result sets. DECLARE @ReturnedParamValue int DECLARE @strForPrinter varchar(100) EXEC udpReturn1ResultSet1Parameter @NumberOfRows = @ReturnedParamValue OUTPUT SET @strForPrinter = ’Number of stored procs: ’ + Cast(@ReturnedParamValue AS varchar(3)) PRINT @strForPrinter GO Re t u r n ing On e St r ing Pa r a m e t e r The code y ou use t o r et urn a st ring value as an out put param et er is essent ially t he sam e code y ou use t o r et urn a num ber value. The m ain dist inct ion is t he declarat ion of t he dat a t ype for t he param et er. The follow ing scr ipt r et urns t he nam e of t he oldest user- defined st or ed procedur e in a dat abase. I t passes back t he nam e of t he st or ed pr ocedur e v ia an out put param et er nam ed @st r Nam eOfOldest SPr oc. Not ice t hat t he out put par am et er declarat ion uses a varchar dat a t ype t hat is consist ent w it h t he m ax im um lengt h of a SQL Serv er ident ifier . I f your applicat ion runs in m ult iple locat ions t hat use different code pages, you m ay want t o use an nvarchar r at her t han a v archar dat a t ype specificat ion for t he param et er. I n t his case, t he t echnique for finding t he st or ed procedure is as int erest ing as t he t echnique for declaring t he out put param et er. The SET ROWCOUNT st at em ent t ells SQL Ser ver t o st op processing a st at em ent aft er t he designat ed num ber of Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  19. records. The ORDER BY clause in t he SELECT st at em ent sort s t he st or ed procedur es so t hat t he nam e of t he oldest st ored pr ocedur e appears first . Ther efor e, st opping aft er pr ocessing t he first r ow r et urns t he oldest st ored procedur e. The t echnique for processing an out put param et er in t he calling r out ine is about t he sam e whet her t he out put param et er has an int or a varchar dat a t y pe. This part icular sam ple appears slight ly sim pler t han t he preceding one m ost ly because it doesn’t label t he ret ur n value t hat is pr int ed in t he Messages pane. Because t he local v ar iable for holding t he out put param et er is alr eady a st ring, t her e is no need t o conv ert it so t hat it can be used as an argum ent for t he PRI NT st at em ent . --CreateudpReturn1StringParameter --Remove prior version of stored procedure. IF EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ’PROCEDURE’ AND ROUTINE_NAME = ’udpReturn1StringParameter’) DROP PROCEDURE udpReturn1StringParameter GO --Create stored procedure to return one --parameter with a string value. CREATE PROC udpReturn1StringParameter @strNameOfOldestSProc varchar(128) OUTPUT AS SET ROWCOUNT 1 SET @strNameOfOldestSProc = (SELECT TOP 1 ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE LEFT(ROUTINE_NAME,3) ’dt_’ AND ROUTINE_TYPE = ’PROCEDURE’ ORDER BY CREATED) GO --Run stored procedure that returns one string parameter. DECLARE @ReturnedParamValue varchar(128) EXEC udpReturn1StringParameter @strNameOfOldestSProc = @ReturnedParamValue OUTPUT PRINT @ReturnedParamValue GO W or k ing w it h Re t ur n St a t u s V a lu e s St or ed pr ocedur es considered t o t his point in t he chapt er proceed in a st raight line fr om t he first t o t he last st at em ent in t he pr ocedur e. However, t his isn’t a requir em ent . Cont rol- of- flow st at em ent s, such as t he I F…ELSE st at em ent , m ake it possible for a st ored procedur e t o ex ecut e condit ionally. You can end t he processing w it hin a st or ed pr ocedur e w it h one or m or e RETURN st at em ent s at t he end of each of several pat hs t hr ough t he code. Each RETURN st at em ent can pass back an int dat a t ype value t o t he calling procedure as it closes t he st or ed procedur e. Alt hough you can have m ult iple RETURN st at em ent s wit h different ret ur n st at us values, any one invocat ion of a st or ed procedur e can r et urn j ust one ret ur n st at us value. This m akes it possible for code inv ok ing a st or ed pr ocedur e t o k now pr ecisely at w hich line t he st ored pr ocedur e closed. The follow ing code sam ple creat es a st or ed procedur e t hat searches for a st or ed procedur e by a nam e in a dat abase. I f t he search finds a st ored pr ocedur e w it h t he t arget nam e, t he r et ur n st at us value is 1. Ot herw ise, t he ret urn st at us value is 0. I t is com m on t o set ret ur n st at us values w it h a RETURN st at em ent inside an I F…ELSE st at em ent ( alt hough t his sam ple’s design is ext raordinar ily sim ple) . Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  20. The calling T- SQL code for t he st or ed procedure in t he follow ing sam ple causes t he procedure t o search for eit her of t wo nam es: udpList ShippersRow or SP1. Make sur e your dat abase has a st ored procedur e nam ed udpList Shipper sRow and t hat y our dat abase doesn’t hav e a st or ed pr ocedur e nam ed SP1. I f you hav e been doing t he sam ples in t he order t hat t hey appear in t his chapt er, y our Chapt er04 dat abase will hav e a st ored pr ocedure nam ed udpList ShippersRow. This let s you use t he sam ple T- SQL code t hat calls t he st ored procedure t o ver ify t hat t he ret ur n st at us values r eflect t he pr esence or absence of a st ored pr ocedur e. The calling T- SQL code for t he st or ed pr ocedur e displays t he r et ur n st at us value in a result set t hat cont ains eit her 0 or 1. These values m at ch each of t he r et ur n st at us values set in t he st ored procedure. The sy nt ax for capt uring a r et ur n st at us value in a calling pr ocedure deviat es slight ly fr om t hat for an out put param et er. I n bot h cases, y ou need a local variable t o r epr esent t he v alue r et ur ned from t he st or ed pr ocedur e. Howev er , t o capt ure t he ret ur n st at us value, y ou use an assignm ent expression t hat set s t he st ored procedure equal t o t he local var iable for t he ret ur n st at us value. This assignm ent expr ession is act ually int egrat ed int o t he call of t he st or ed procedur e as an argum ent for an EXEC st at em ent . I n t he sam ple, a local v ariable specifies t he v alue for t he procedure t o pass t o t he st ored procedure. As t he code appears, t he calling code passes t he nam e udpList ShippersRow. Howev er, y ou can com m ent out ( w it h t wo leading hyphens) t he assignm ent st at em ent for t he @st r ProcNam e local var iable and rem ov e t he hyphens from t he assignm ent st at em ent t hat set s t he local variable t o SP1. This t ransit ion w ill cause t he r et urn st at us value t o swit ch fr om 1 t o 0. --CreateudpReturnStatusValue --Remove prior version of stored procedure. IF EXISTS (SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ’PROCEDURE’ AND ROUTINE_NAME = ’udpReturnStatusValue’) DROP PROCEDURE udpReturnStatusValue GO --Create stored procedure to pass back --a return status value of 0 or 1. CREATE PROC udpReturnStatusValue @strName varchar(123) AS SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = @strName AND ROUTINE_TYPE = ’PROCEDURE’ IF @@ROWCOUNT = 0 RETURN 0 ELSE RETURN 1 GO --Pass a procedure name to udpReturnStatusValue. DECLARE @strProcName varchar(128) DECLARE @return_status int --Use the following SET statement for a 1. SET @strProcName = ’udpListShippersRow’ --Use the following SET statement for a 0. --SET @strProcName = ’SP1’ EXEC @return_status = udpReturnStatusValue @strProcName SELECT @return_status AS ’Return Status’ Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

CÓ THỂ BẠN MUỐN DOWNLOAD

Đồng bộ tài khoản