--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

Cont ra sting Un e n cr ypt ed and Encrypt ed View s Wit h m inor ext ensions, the preceding sam ple can serve as a t em plat e for t he creat ion of any view. 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 the Nort hwind dat abase as it s base t able. While t he row source for a view can reside

in anot her dat abase, t he CREATE VI EW st at em ent can creat e a view only in t he current dat abase. Sim ilarly, t he DROP VI EW st at em ent can rem ove a view only from t he current dat abase. An easy way t o r eference a row source fr om anot her SQL Server 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 owner is t he default dbo user, you can om it it s explicit designat ion ( as in t he follow ing scr ipt ). The t hird nam e part denotes t he nam e of t he dat abase obj ect providing t he row source for a view . Figure 4- 1 shows t he resu lt set from t he SELECT st at em ent based on t he vewShippers view. Not ice t hat it m at ches t he values in t he Nort hwind..Shippers table, which is the source for t he v ewShippers view. Not ice t hat unlike t he first code sam ple, t his one doesn’t include a specific reference t o t he Chapt er04 database. That ’s because Query Analyzer w ill cont inue to use Chapter04 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.

The ENCRYPTI ON at t r ibut e isn’t set by default . Set t ing encrypt ion doesn’t change the result 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 verify t his by t r ying t o disp lay t he script for a view. The VI EW_DEFI NI TI ON colum n for t he I NFORMATI ON_SCHEMA.VI EWS view ret urns the script for a v iew on each of it s r ow s. The follow ing scr ipt dem onst rat es t he sy ntax for invoking t he ENCRYPTI ON at t ribut e. The scr ipt 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 cr eat ing t he v iew; t hese st at em ents include the 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 view . How ever, 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 view. After cr eat ing t he view, the script perfor m s a sim ple SELECT query t o v erify t he cont ent s of t he view ’s result set . Th e final port ion of t he script creat es anot her result set w ith t he definit ion for each user- defined view in t he current dat abase, which is Chapt er04 in t he sam ple. Om it t ing all row s beginning wit h “sys” for t heir TABLE_NAME colum n value in t he I NFORMATI ON_SCHEMA.VI EWS view excludes all syst em view s from the 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

Sor t ing a n d Grouping W it hin a Vie w The SELECT st at em ent t hat def ines a view has generally t he sam e sy nt 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 works t he sam e in both st and- alone script s and t hose inside view s. Sim ilarly, t he I N keyword in a WHERE clause works the sam e as well. I n cont rast , t he ORDER BY clause in a SELECT st at em ent requires 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 requires the TOP predicat e aft er t he SELECT keyword. The TOP predicat e, in t urn, r equires an argum ent t o designat e how m any records t o ret ur n. I f you want all t he rows from a source, follow TOP wit 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 keyword ret urns the first 10 rows in t he result set . When you use an ORDER BY clause, t hose row s will be the highest or lowest colum n values on a sort dim ension depending on the sort order. The sy nt ax for designat ing a sort order in an ORDER BY clause is the sam e in a SELECT st at em ent in or out of a view . The follow ing scr ipt shows the cr eat ion and ret urn of values from a view 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 filters exclusively for countries 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 wit hin count r y. Th e SELECT st at em ent in t he follow ing scr ipt is dist inct because of it s use of t he TOP predicat e. While t he TOP predicat e will w ork in a st and-alone script , 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. 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

View s for Rem ot e a nd H et erogeneous Sources I t is oft en necessary t o view dat a r esiding on anot her SQL Server inst ance or even in anot her t ype of dat abase form at . T- SQL provides sever al appr oaches t o sat isfying t hese kinds of requirem ent s. The OPENROWSET funct ion is a flex ible approach because it can accom m odat e ad hoc queries as well as t hose perform ed on a regular basis. As m ent ioned prev iously, Books Online recom m ends t hat y ou use link ed ser vers when it is necessary t o query a rem ot e or het er ogeneous source on a regular basis. Howev er, you can invoke t he OPENROWSET funct ion for a userid t hat doesn’t have m em bership in t he sysadm in or set upadm in fixed server roles. 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 rem ot e dat a access. Creat ing a View for Anot her SQL Ser ver I n st a nce One t ypical requirem ent is t o view a SQL Serv er row source, such as a t able, on anot her server. You can use t he OPENROWSET funct ion to perform t his task , wit h argum ent s t hat sp ecify a prov ider, ot her elem ents of a connect ion st ring, and a SELECT st at em ent . The OPENROWSET funct ion can serve 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 your goal is t o creat e a v iew in t he current dat abase t hat exposes a row source in anot her dat abase. When the 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 Server inst ance, t he provider for t he funct ion should be SQLOLEDB. Next you can denot e t he rem aining elem ents of t he connect ion st ring for t he ot her server in t he follow ing order: t he ser ver inst ance nam e, a SQL Serv er login for t he server , and a passw ord 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 server nam e and login nam e. A com m a separat es t he passw ord from t he SELECT st at em ent . The follow ing scr ipt cr eat es a view on one SQL Server running SQL Server 2000 that point s at a t able on t he cabxli ser ver running t he MSDE version com pat ible wit h SQL Server 7. You need t w o inst ances of SQL Server t o evaluat e t his scr ipt , Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.but you can nam e t he inst ances anyt hing you want. Just ch ange t he references to cabxli to t he nam e of a SQL Server inst ance t o which you can connect . By t he way, t he table is t he aut hors table 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. Product ion servers should always have a password for t he sa login if you aren’t forcing Windows aut hent icat ion. The SELECT st at em ent refer ences t he aut hors t able in the pubs dat abase on t he cabxli server. 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 akes t he OPENROWSET funct ion as t he argum ent for it s FROM clause. The SELECT list for t he outer 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, au_fname’) GO

--Select all rows and columns from the --vewAuthorsSortedOnCabxli view in Chapter04. SELECT * FROM vewAuthorsSortedOnCabxli GO

Creat ing a View for an Acce ss Dat aba 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 Server solut ion. While you can perform a full-scale upsizing, it is possible t hat t he OPENROWSET funct ion can dram at ically reduce the effort of w orking w it h Access dat a from SQL Server. That ’s because t he funct ion perm it s a SQL Server solut ion t o view Access dat a wit hout t he need of transport ing t he dat a from Access t o SQL Server. Therefore, you save t he conversion effort . I n addit ion, your client s avoid the disrupt ion t hat could arise if their fam iliar Access solut ion were unavailable because you replaced it wit h a SQL Server 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 to exper ience bot t leneck s relat ed t o the capacit y of t he Access dat abase, this approach bears considerat ion. I n any event , the approach support s t he easy availabilit y of Access dat a from SQL Server views. You can use an OPENROWSET funct ion t o connect wit h an Access dat abase m uch like you use t he funct ion t o connect w it h a SQL Server dat abase on anot her SQL Server 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 database file, a login nam e, and a passw ord. Th e OPENROWSET funct ion also has it s ow n SELECT Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.st at em ent t hat specifies t he row source in t he Access dat abase as w ell as any special set t ings, such as a WHERE clause. The follow ing scr ipt dem onst rat es a connect ion to an Access dat abase file on the current com put er. The pat h points 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 wit h an em pt y password. This is norm al for an unsecured Access dat abase file, such as t he Access Nort hw ind sam ple. Th e SELECT st at em ent inside the OPENROWSET funct ion call designat es t he retur n of all rows wit 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 ever, w it hin the OPENROWSET funct ion, single quot at ion m arks are alr eady used around the SELECT st at em ent , so it ’s necessary t o use t wo single quot at ion m arks on each side of USA. I n t he following script , t he out er SELECT st at em ent disp lays all t he colum ns from the 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

--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

Joining Row Sour ces for a View The value of being able t o pr ocess rem ot e and het erogeneous dat a sources m ult iplies when you can j oin t wo row sources from different serv ers or different dat abases. There 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 approach, each side of t he j oin has it s own explicit OPENROWSET funct ion. The ot her approach is to cr eat e t w o new views, each based on it s own OPENROWSET funct ion. Then y ou can cr eat 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 row sour ces from differ ent dat abase servers in different dat abase form at s! The follow ing scr ipt shows the sy nt ax for t he first approach. Like several of t he previous OPENROWSET funct ion sam ples, t his one r equires t wo inst ances of SQL Server. The scr ipt j oins rows fr om t he Or ders t able in a SQL Server dat abase w it h rows from t he Cust om ers t able in an Access dat abase file. The OPENROWSET funct ion declarat ions follow t he synt ax of prev ious sam ples t hat used t he funct ions separately as t he source for a view . This scr ipt sam ple j oins t he Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.Cust om ers r ows wit h the Or ders 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 your applicat ion doesn’t require separat e v iews for each row source obj ect t hat get s j oined. This saves your applicat ion from opening t he views. --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

Re t u r ning a Sor t ed Result Set Even a basic SELECT st at em ent can yield benefit s w hen it is m ade available from a st ored pr ocedure. For exam ple, t he use of t he ORDER BY clause in a v iew requires t he concu rrent use of t he TOP predicat e. While t his is cert ainly not com plicat ed, it is j ust one m ore t hing you hav e t o rem em ber t o get right . The synt ax for using the ORDER BY clause in a st ored procedure is just like t hat in a st and-alone T- SQL script . I n ot her words, you don’t need a TOP predicat e for your SELECT st at em ent . The follow ing scr ipt shows the ORDER BY clause wit hin a SELECT st at em ent that det erm ines t he result set from a st ored procedure. The SELECT st at em ent generat es a resu lt set based on t he Shippers t able, w it h t he rows 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 script again relies on a three- part st rat egy. The first part rem oves an old version of t he udpShippersSort edBy Com panyNam e st ored procedure. The second part invokes the CREATE PROC st at em ent t o add t he new st ored procedur e. The t hird part runs the newly cr eat ed st ored procedure w it h the 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 ored procedure. There is no need for anyt hing else aft er t he EXEC key 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.GO

--Create udpShippersSortedByCompanyName with an --input parameter to specify a row. CREATE PROC udpShippersSortedByCompanyName AS SELECT * FROM Northwind..Shippers ORDER BY CompanyName 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

--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

W or king w it h Re t ur n St a t u s V a lues Stor ed pr ocedures considered t o t his point in t he chapt er proceed in a st raight line fr om t he first to t he last st at em ent in t he procedure. However, t his isn’t a requirem ent . Cont rol- of- flow st at em ents, such as t he I F…ELSE st at em ent , m ake it possible for a st ored procedur e t o execu te condit ionally. You can end the processing w it hin a st ored procedure wit h one or m ore RETURN st at em ent s at the end of each of several pat hs t hrough t he code. Each RETURN st at em ent can pass back an in t dat a t ype value t o t he calling procedure as it closes t he st ored procedure. Alt hough you can have m ult iple RETURN st atem ent s wit h different ret ur n st at us values, any one invocat ion of a st ored procedure 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 ored procedure to k now precisely at w hich line t he st ored procedure closed. The follow ing code sam ple creat es a st ored procedure t hat searches for a st ored procedure by a nam e in a dat abase. I f t he search finds a st ored procedure wit h the t arget nam e, t he r et ur n st at us value is 1. Ot herwise, 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 with 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 raordinarily sim ple) . Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.The calling T- SQL code for t he st or ed procedure in t he following sam ple causes the procedure t o search for eit her of t wo nam es: udpList Sh ippersRow or SP1. Make sur e your dat abase has a st ored procedur e nam ed udpList Shipper sRow and that your dat abase doesn ’t have a st or ed pr ocedure nam ed SP1. I f you have been doing t he sam ples in t he order t hat t hey appear in this chapt er, your Chapt er04 dat abase will have a st ored procedure nam ed udpList ShippersRow. This let s you use t he sam ple T- SQL code t hat calls t he st ored procedure to verify t hat t he ret ur n st at us values reflect the presence or absence of a st ored procedur e. The calling T- SQL code for t he st or ed pr ocedure displays t he ret ur n st at us value in a result set that cont ains eit her 0 or 1. These values m at ch each of t he ret urn st at us values set in t he st ored procedure. The sy nt ax for capt uring a ret urn st at us value in a calling procedure deviates slight ly fr om t hat for an out put param et er. I n bot h cases, you need a local variable to represent t he value r et urned from t he st ored procedure. However , t o capt ure t he ret ur n st at us value, you use an assignm ent expression t hat set s t he st ored procedure equal to the local variable 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 ored procedur e as an argum ent for an EXEC st at em ent . I n t he sam ple, a local variable 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. However, you can com m ent out ( w it h two leading hyphens) the assignm ent st at em ent for t he @st r ProcNam e local variable and rem ove t he hyphens from t he assignm ent st at em ent t hat set s t he local variable t o SP1. This transit ion will cause t he ret urn st at us value t o sw it 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