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

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


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

colum n of t he I NFORMATI ON_SCHEMA.VI EWS view cannot expose t he T- SQL t hat
generat es t he view .
Figu re 4 - 2 . An e xce r pt show ing th e resu lt set fr om a n e ncryp t ed view a s
w e ll as t h e VI EW _ D EFI N I TI ON colum n va lu es from t h e
I N FORM ATI ON _ SCH EM A.V I EW S vie w for t h re e view s in a dat abase .

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

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

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

’admin’;’’,
’SELECT * FROM Customers WHERE Country=‘‘USA’’’)
GO

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

Creat ing a View for an 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 provider, which 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
through it s SELECT st at em ent let s your applicat ions ch oose a specific row source
wit hin a dat a source or even filt er a r ow source t o der ive a new cust om source for
an applicat ion.
Using t he OPENROWSET funct ion t o connect with 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 wit h SQL
Server and Jet r ow sources. The m ain differences are in the connect ion st ring
specificat ions. Fir st y ou m ust designat e t he MSDASQL provider inst ead of t he
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
SQLOLEDB or Jet provider. Second you specify connect ion st ring elem ent s t hat
are appropr iat e for t he dat a source t o which y ou want t o connect .
The follow ing scr ipt shows the sy nt ax for an applicat ion of t he OPENROWSET
funct ion w it 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 Server dat a source wit h t he ODBC dr iver, but t he general
synt ax issues are 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 replace t he reference t o

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


The next script shows t he sy nt ax for t he alt ernat ive approach t o j oining t wo
het er ogeneous dat a sources. Again, y ou need t wo SQL Server inst ances t o run
the sam ple. Th is alt ernat ive j oins t wo pr eviously creat ed v iews. I n this inst ance,
each view is from a prior sam ple in t his chapt er. I n addit ion, t he t wo views
corr espond t o t he SELECT st at em ent s for each of t he nest ed OPENROWSET
funct ions in the prior sam ple. Therefore, t he result is ident ical for t he next scr ipt
and t he prior scr ipt . Howev er, t he code for t he next scr ipt is dram at ically sim pler.
By segm ent ing t he t wo OPENROWSET funct ions int o separat e views, 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 requires the addit ional overhead of m anaging t wo
separat e views. This includes cr eat ing, m aint aining, and opening t he v iew s.
--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.

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

st at us values. When a st ored procedure can follow any of several int er nal
processing pat hs, ret urn st at us values can indicat e t o a calling rout ine which pat h
a st ored pr ocedure pursued.
A second m aj or use of st ored procedures is t he processing of input param et ers.
These param et ers enable your applicat ions t o cont rol dynam ically t he t hings t hat
a st ored pr ocedure ret urns. Not all T- SQL st at em ent s take param et ers. I n t hese
circum st ances, you can com bine the use of param et ers wit 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 cr it erion values for WHERE
clause expr essions, your applications can dy nam ically control a st ored
procedure’s result set . When users set t he par am et er values, you enable users t o
cont rol an applicat ion dynam ically at run t im e.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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 row sources. I n t his cont ext , a st ored
procedure prov ides value t o an applicat ion w it hout r et urning a r esult set , a
param et er value, or a return st at us value. The procedure sim ply m odifies a row
source. Because st ored procedures can set param et ers based on user input and
the procedures can use param et ers for insert / update/ delet e operat ions, users can
cont rol t he m odificat ions to a row source at run t im e.
Fourt h, you w ill learn how t o use st ored procedures as program 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 procedures. 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 rol- of-
flow st at em ent s, such as I F…ELSE st at em ent s. I n addit ion, you can sp ecify any of
four t ypes of values— local variables, global variables, 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 ored procedure and how it
com m unicat es wit h it s calling procedure.
N ot e

CREATE PROC
procedurename
Parameter specifications

AS
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

T-SQL code Aft er you cr eat e a st ored procedure, y ou can ch ange it s code in at least t wo
different w ays. First , you can invoke t he DROP PROCEDURE ( or DROP PROC)
st at em ent t o rem ove t he pr ior version and t hen invoke a new CREATE PROC
st at em ent wit h t he sam e nam e as t he rem oved procedure. To delet e an exist 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 the nam e of t he st ored procedure t hat you want t o rem ove. Wit h t his
approach, you w ipe out any perm issions assigned t o users for t he dropped st ored
procedure. Alt ernat ively, you can invoke t he ALTER PROCEDURE ( or ALTER PROC)
st at em ent . This allows you 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 tings for t he st ored
procedure t hat y ou m odify. Except for t he keyword declaring 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
procedure 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 keyword w it h t he nam e of t he st ored procedur e
that you want t o r un. The sy nt ax for t he EXEC st at em ent perm it s you t o assign
values for input param et ers as well as accept out put param et er and ret urn st at us
values. I n addit ion, t he EXEC st at em ent can also ret urn 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 oking st ored

rem ainder of t he local v ariable nam e m ust follow st andard SQL Server ident ifier
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
convent ions. The DECLARE st at em ent for a local variable m ust include a dat a
type for t he var iable. You can use any dat a t ype except for t ext , nt ext, and
im age. A local variable’s dat a t ype specification det erm ines t he t ype of cont ent
that t he var iable can hold. Local var iables can be used in expr essions and as
argum ent s for cont rol- of-flow st at em ent s t o cont rol t he operat ion of a st ored
procedure. Local var iables can w or k in coordinat ion wit h param et ers by accept ing
values from param et ers and passing values t o t hem .
Dev elopers fam iliar wit h SQL Server v ersions pr ior t o 7.0 m ay be fam iliar wit h t he
term global variables. SQL Server 2000 refers t o t hese global variables as
funct ions. A global variable funct ion nam e st art s wit h @@. These global var iable
funct ions ret ur n values t o st or ed procedures t hat cont ain sy st em inform at ion. You
can disp lay t he full list of 33 @@ var iable funct ions from the I ndex tab 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 egularly find part icular ly convenient
include @@I DENTI TY, @@ERROR, and @@DBTS. Th ese t hr ee funct ions ret urn
the 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 cu rrent t im est am p value w it hin a dat abase. Creat ing a n d Using St ored Procedures
The purpose of t his sect ion is t o int roduce you t o syntax for creat ing and using
st ored procedures. This sect ion shows you t ypical ways of applying t he CREATE
PROC st at em ent . I n addit ion, you learn com m on ways of specifying t he EXEC
st at em ent t o run a st ored procedure. The sect ion illust rat es t echniques for
designat ing input param et ers when you cr eat e a st ored pr ocedure as well as
ways of specifying input param et er values when you run a st or ed procedure.
Dyna m ica lly Select in g fr om a Row Sour ce

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

ORDER BY CompanyName
GO

--Run udpShippersSortedByCompanyName.
EXEC udpShippersSortedByCompanyName
GO

Re t u r ning t h e Scr ipt for a View
Stor ed pr ocedures are an ext rem ely flexible t ool. You can use SELECT stat em ent s
in t he full range of cases t hat use view s and st and-alone T- SQL st at em ent s. For
exam ple, you can query I NFORMATI ON_SCHEMA views t o uncov er inform at ion
about t he obj ect s in a dat abase. An advant age of a st or ed pr ocedure 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
before SQL Server can use it . Therefore, t he st ored procedure can r un t he sam e
T-SQL code fast er.
N ot e
The sp_executesql syst em st ored procedure offers som e of
the benefit s of st ored procedures for st and- alone T- SQL
SELECT st atem ent s.
The follow ing scr ipt dem onst rat es t he use of a st ored pr ocedure t o query t he
I NFORMATI ON_ SCHEMA.VI EWS view. The resu lt set for t his view cont ains a r ow
for each view 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 view. The TABLE_NAME colum n ret urns t he
nam e for a view.
The st ored procedure accept s a param et er t hat designat es a view ’s nam e. The
st ored procedure’s SELECT st at em ent passes t he T- SQL scr ipt for a view t o a local
variable, @st rDefinit ion. The local variable accept s t he value in the
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 ored pr ocedure. Then a PRI NT st at em ent
displays t he cont ent s of t he local variable in t he Messages pane.


--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 Processing St ored Procedure Output s
One of t he t asks t hat st ored procedures serve especially well is get t ing dat a back
to a calling procedure. St or ed pr ocedures can achieve t his goal in several ways.
First , t hey perm it t he t ransf er of dat a back t o t he calling procedure in t he form of
result set s. You can ret urn m ult iple result set s from a single st ored procedur e.
Second, a st ored procedure can r et urn scalar values via out put param et ers.
Third, code calling a st ored pr ocedure can process ret urn st at us values. I n any
one applicat ion, y ou can concu rrent ly use any com binat ion of t hese t hree
processes for ret ur ning values. This sect ion elaborat es on t hem and dem onst rat es
the sy nt ax for im plem ent ing each .
Re t u r ning Tw o Result Set s fr om a St or ed Procedu r e
I t ’s sim ple t o ret ur n m ult iple resu lt set s from a single st ored pr ocedure: j ust

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

Figure 4- 3 sh ow s t he out put from running t he udpRet urn2Result Set s st ored
procedure. (This is t he out put from the preceding script .) Not ice t hat t he t op
result set cont ains ROUTI NE_NAME and CREATED colum n values. This resu lt has
a row for each user -defined st ored procedure. The last row includes t he nam e and
creat ion dat e for t he elevent h st ored procedure. The second result set cont ains a
num ber t hat is t he count of t he num ber of user- defined st ored procedures— 11.
Figur e 4 - 3 . Th e ret u r n fr om a u ser- d efined st ored pr oced ur e that
spe cifies t w o resu lt se ts.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Re t u r ning One Result Set and One Pa ra m et e r Va lue

because you cannot w ork direct ly wit h t he out put param et er in t he code that calls
the st or ed pr ocedure. The sam ple code declares a local variable nam ed
@Ret urnedParam Value t o st ore t he out put param et er value locally. Second you
need an assignm ent st atem ent . This st at em ent m ust end wit h t he OUTPUT
keyw ord. I n addit ion, t he local variable m ust be on t he right side of t he equal
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
sign, and t he out put par am eter should appear on t he left side. Th ird t he out put
param et er ret ur ns an int dat a t ype value. However, t he Print st at em ent that
report s t he num ber of st ored procedures r equires a charact er dat a t ype, nam ely
varchar. Therefore, t he code applies the CAST funct ion t o t he local variable
st oring t he out put param et er value; 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 ring const ant wit h t he
CAST funct ion value. The PRI NT st atem ent t akes @st rForPrint er as it s argum ent
to print t he num ber of st ored procedures wit 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

I n t his case, t he t echnique for finding t he st ored procedure is as int erest ing as
the t ech nique for declaring t he out put param et er. The SET ROWCOUNT st at em ent
tells SQL Server 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.
records. The ORDER BY clause in t he SELECT st at em ent sorts t he st ored
procedures so t hat the nam e of t he oldest st ored procedur e appears first .
Ther efor e, st opping after processing the first row ret urns t he oldest st ored
procedure.
The t echnique for processing an out put param et er in t he calling r out ine is about
the sam e whet her t he out put param eter has an int or a varchar dat a t ype. This
part icu lar 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 already a st ring, t her e is no
need t o convert 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

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

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.