Cha pt e r 4 . Pr ogr a m m ing Vie w s a nd St or e d Pr oce dur e s in VB.NET

Creation Data Matrix in VB.NET Cha pt e r 4 . Pr ogr a m m ing Vie w s a nd St or e d Pr oce dur e s

The preceding chapt er int r oduced y ou t o program m ing dat a access wit h T- SQL. This chapt er builds on and goes beyond t he int r oduct ion in t w o explicit ways: First it int roduces v iews by descr ibing t heir uses wit h various t ypes of r ow sources. Second it int r oduces you t o st ored procedur es by rev iew ing t heir uses and t he st at em ent s for creat ing and alt ering t hem , and by focusing on t he use of param et ers and local variables t hat are oft en found in st or ed pr ocedur es. A view is a cont ainer for a single SELECT st at em ent . Your SQL Serv er applicat ions can r efer t o t he v iew nam e as a short cut t o t he SELECT st at em ent w it hin t he view. I n t his chapt er, y ou w ill lear n t he sy nt ax for creat ing and using v iews. Special at t ent ion goes t o cr eat ing views for dat a on rem ot e serv ers and for dat a in ot her dat abase form at s, such as Access and any ODBC dat a source. St or ed pr ocedur es are com piled set s of T- SQL st at em ent s. Aft er int r oducing t he synt ax for creat ing st or ed pr ocedures, t he chapt er drills dow n on t he sy nt ax for m anipulat ing param et er s and ret ur n st at us values, pr ogram m ing t he insert ion and delet ion of r ows as well as t he updat ing of colum n values in row sources, and t he r et ur n of condit ional r esult set s fr om a st or ed procedure. The resources for t his chapt er include a dat abase, Chapt er04, w it h com plet ed versions of t he sam ple views and st or ed procedur es discussed as well as T- SQL script s for creat ing t he views and st or ed procedur es from scrat ch. Unless explicit ly st at ed, all script s should be r un fr om t he Chapt er04 dat abase. See t he Chapt er Resources sect ion in Chapt er 2 for m or e det ail on at t aching dat abase files t o a serv er and cr eat ing a new blank dat abase fr om w hich you can inv ok e t he script s. The chapt er also r efer ences ot her com m only available dat abases, including t he SQL Ser ver Nort hw ind dat abase, t he pubs dat abase, and t he Access Nort hwind dat abase. The first t wo dat abases ar e inst alled w it h SQL Ser ver; t he t hird dat abase is inst alled w it h Access. For t he references t o r em ot e serv ers, y ou will need an inst ance of SQL Ser ver r unning on t wo different com put ers or t w o inst ances of SQL Serv er r unning on t he sam e com put er .