ptg
884
CHAPTER 28 Creating and Managing Stored Procedures
. SELF—This option is equivalent to EXECUTE AS user_name, where the specified user is
the person creating or modifying the stored procedure.
. OWNER—This option specifies that the statements inside the stored procedure execute
in the context of the current owner of the stored procedure. If the procedure does
not have a specified owner, the owner of the schema in which the procedure was
created is used.
OWNER must map to a single user account and cannot be a role or
group.
. ’user_name’—This option specifies that the statements inside the stored procedure
execute in the context of the user_name specified. Permissions for any objects within
the stored procedure are verified against this user. The user specified must exist in
the current database and cannot be a group, role, certificate, key, or built-in account.
To determine the execution context of a stored procedure, you can query the
execute_as_principal_id column in either the sys.sql_modules or
sys.assembly_modules catalog view.
Specifying an execution context for a stored procedure can be very useful when you want
to define custom permission sets. For example, some actions, such as TRUNCATE TABLE,
cannot be explicitly granted to other users. However, if you use the EXECUTE AS clause to
set the execution context of a stored procedure to a user who does have truncate table
permissions (for example, a user who has permissions to alter the table), you can then
incorporate the
TRUNCATE TABLE statement within the procedure. Any user to whom you
then grant EXECUTE permission on the stored procedure is able to run it to execute the
TRUNCATE TABLE command contained in it.
TIP
When using the EXECUTE AS clause to customize the permission set for a stored pro-
cedure, it is good security policy to specify a login or user that has the least privileges
required to perform the operations defined in the stored procedure. Do not specify an

account such as a database owner account unless those permissions are required.
To specify the EXECUTE AS clause when you create or modify a stored procedure and
specify a user account other than your own, you must have impersonate permissions on
the specified user account in addition to having permissions to create or alter the stored
procedure. When no execution context is specified or
EXECUTE AS CALLER is specified,
impersonate permissions are not required.
The following example demonstrates how the user context changes when you use the
EXECUTE AS clause in the creation of a stored procedure:
use bigpubs2008
go
sp_addlogin fred, fred2008
go
Download from www.wowebook.com
ptg
885
Deferred Name Resolution
28
sp_grantdbaccess fred
go
create proc test_execute_as
with EXECUTE AS ‘fred’
as
select user_name() as ‘User context within proc’
go
select user_name() as ‘User context before EXEC’
exec test_execute_as
User context before EXEC

dbo

User context within proc

fred
Deferred Name Resolution
In SQL Server 2008, the object names that a stored procedure references do not have to
exist at the time the procedure is created. SQL Server 2008 checks for the existence of
database objects at the time the stored procedure is executed and returns an error message
at runtime if the referenced object doesn’t exist. The only exception is when a stored
procedure references another stored procedure that doesn’t exist. In that case, a warning
message is issued, but the stored procedure is still created (see Listing 28.6).
LISTING 28.6 Procedure Name Resolution During Stored Procedure Creation
create proc p2
as
exec p3
go
The module ‘p2’ depends on the missing object ‘p3’. The module will still be
created; however, it cannot run successfully until the object exists.
When a table or view does exist at procedure creation time, the column names in the refer-
enced table are validated. If a column name is mistyped or doesn’t exist, the procedure is
not created (see Listing 28.7).
Download from www.wowebook.com
ptg
886
CHAPTER 28 Creating and Managing Stored Procedures
LISTING 28.7 Column Name Validation in Stored Procedures
IF EXISTS ( SELECT * FROM sys.procedures
WHERE schema_id = schema_id(‘dbo’)
AND name = N’get_authors_and_titles’)
DROP PROCEDURE dbo.get_authors_and_titles
GO

create proc get_authors_and_titles
as
select a.au_lname, au_fname, title, isbn_number
from authors a join titleauthor ta on a.au_id = ta.au_id
join titles t on t.title_id = ta.title_id
return
go
Server: Msg 207, Level 16, State 1, Procedure get_authors_and_titles, Line 4
Invalid column name ‘isbn_number’.
One advantage of delayed (or deferred) name resolution is the increased flexibility when
creating stored procedures; the order of creating procedures and the tables they reference
does not need to be exact. Delayed name resolution is an especially useful feature when a
stored procedure references a temporary table that isn’t created within that stored proce-
dure. However, at other times, it can be frustrating to have a stored procedure create
successfully only to have it fail when it runs due to a missing table, as shown in
Listing 28.8.
LISTING 28.8 Runtime Failure of a Stored Procedure with an Invalid Object Reference
create proc get_authors_and_titles
as
select a.au_lname, au_fname, title, pub_date
from authors a join titleauthor ta on a.au_id = ta.au_id
join books t on t.title_id = ta.title_id
go
EXEC get_authors_and_titles
go
Server: Msg 208, Level 16, State 1, Procedure get_authors_and_titles, Line 4
Invalid object name ‘books’.
Download from www.wowebook.com
ptg
887

Deferred Name Resolution
28
Another issue to be careful of with deferred name resolution is that you can’t rename
objects referenced by stored procedures and have the stored procedure continue to work.
In versions of SQL Server prior to 7.0, after the stored procedure was created, object refer-
ences within the stored procedure were made via the object ID rather than the object
name. This allowed stored procedures to continue to function properly if a referenced
object was renamed. However, now that object names are resolved at execution time, the
procedure fails at the statement referencing the renamed object. For the stored procedure
to execute successfully, it needs to be altered to specify the new object name.
Identifying Objects Referenced in Stored Procedures
Because changing the name of a table can cause stored procedures to no longer work, you
might want to identify which stored procedures reference a specific table so you know
which stored procedures will be affected by changes to the table name or columns. You
can view the dependencies between database objects by querying the
sys.sql_dependencies
object catalog view. Unfortunately, all you really see if you query the sys.sql_dependencies
view is a bunch of numbers—just the IDs of the objects and columns that have a depen-
dency relationship, along with some additional status information.
The better way to display a list of stored procedures that reference a specific table or view,
or to display a list of objects referenced by a stored procedure, is to use the
sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities dynamic
management functions.
sys.dm_sql_referencing_entities ( ‘ schema_name.table_or_view_name ‘ , ‘ OBJECT ‘ )
sys.dm_sql_referenced_entities ( ‘ schema_name.proc_name ‘ , ‘ OBJECT ‘ )
For example, to display the stored procedures, triggers, functions, and views that reference
the titles table, you would execute the following:
select referencing_schema_name, referencing_entity_name
From sys.dm_sql_referencing_entities ( ‘dbo.titles’ , ‘OBJECT’ )
go

In the bigpubs2008 database, the titles table is referenced by the following:
referencing_schema_name referencing_entity_name

dbo AverageBookPrice
dbo AverageBookPrice2
dbo AveragePricebyType
dbo AveragePricebyType2
dbo reptq1
dbo reptq2
dbo reptq3
dbo title_authors
Download from www.wowebook.com
ptg
888
CHAPTER 28 Creating and Managing Stored Procedures
dbo titleview
dbo valid_book_types
To display the objects referenced by the title_authors stored procedure, you could execute
the following:
select distinct
referenced_entity_name as table_name,
referenced_minor_name as column_name
From sys.dm_sql_referenced_entities (‘dbo.title_authors’ , ‘OBJECT’ )
go
In the current database, the specified object references the following:
table_name column_name

authors NULL
authors au_fname
authors au_id

authors au_lname
titleauthor NULL
titleauthor au_id
titleauthor title_id
titles NULL
titles title
titles title_id
You can also see dependency information in SSMS by right-clicking an object and choos-
ing View Dependencies. This brings up the Object Dependencies window, as shown in
Figure 28.4. You can view either the objects that depend on the selected object or objects
on which the selected object depends. You can also expand the dependency tree for the
objects listed in the Dependencies pane.
Viewing Stored Procedures
You can view the source code for stored procedures in SQL Server 2008 by querying the
definition column of the object catalog view sys.sql_modules or by using the system
procedure sp_helptext (see Listing 28.9).
LISTING 28.9 Viewing Code for a Stored Procedure by Using sp_helptext
exec sp_helptext title_authors
go
Text

CREATE PROCEDURE title_authors
Download from www.wowebook.com
ptg
889
Viewing Stored Procedures
28
FIGURE 28.4 Viewing object dependencies in SSMS.
By default, all users have permission to execute sp_helptext to view the SQL code for the
stored procedures in a database. If you want to protect the source code of stored proce-

dures and keep its contents from prying eyes, you can create a procedure by using the
WITH ENCRYPTION option. When this option is specified, the source code stored in the
database is encrypted.
NOTE
If you use encryption when creating stored procedures, be aware that although SQL
Server can internally decrypt the source code, no mechanisms exist for the user or for
any of the end-user tools to decrypt the stored procedure text for display or editing. With
this in mind, make sure that you store a copy of the source code for those procedures
in a file in case you need to edit or re-create them. Also, procedures created by using
the
WITH ENCRYPTION option cannot be published as part of SQL Server replication.
AS
BEGIN
SELECT a.au_lname, a.au_fname, t.title
FROM titles t INNER JOIN
titleauthor ta ON t.title_id = ta.title_id RIGHT OUTER JOIN
authors a ON ta.au_id = a.au_id
RETURN
END
Download from www.wowebook.com
ptg
890
CHAPTER 28 Creating and Managing Stored Procedures
You can, however, attach a debu gger to the ser ver proc ess and retr ieve a decr ypte d
procedure from memory at runtime.
You can also view the text of a stored procedure by using the ANSI INFORMATION_SCHEMA
view routines. The routines view is an ANSI standard view that provides the source code
for the stored procedure in the routine_description column. The following example uses
the INFORMATION_SCHEMA.routines view to display the source code for the title_authors
stored procedure:

select routine_definition
from INFORMATION_SCHEMA.routines
where specific_catalog = ‘bigpubs2008’
and specific_schema = ‘dbo’
and routine_type = ‘Procedure’
and routine_name = ‘title_authors’
go
routine_definition

CREATE PROCEDURE title_authors
AS
BEGIN
SELECT a.au_lname, a.au_fname, t.title
FROM titles t INNER JOIN
titleauthor ta ON t.title_id = ta.title_id RIGHT OUTER JOIN
authors a ON ta.au_id = a.au_id
RETURN
END
However, the routine_description column is limited to only the first 4,000 characters of
the stored procedure code. A better way to view the code with a query is to use the
sys.sql_modules object catalog view:
select definition
from sys.sql_modules
where object_id = object_id(‘title_authors’)
go
CREATE PROCEDURE title_authors
AS
BEGIN
SELECT a.au_lname, a.au_fname, t.title
FROM titles t INNER JOIN

titleauthor ta ON t.title_id = ta.title_id RIGHT OUTER JOIN
authors a ON ta.au_id = a.au_id
Download from www.wowebook.com
ptg
891
Modifying Stored Procedures
28
RETURN
END
Finally, one other method of displaying the source code for a stored procedure is to use
the new object_definition() function. This function takes the object ID as a parameter.
If you, like most other people, do not know the object ID of the procedure in question,
you can use the
object_id() function. The following is an example of using the
object_definition() function:
select object_definition(object_id(‘dbo.title_authors’))
go

CREATE PROCEDURE title_authors @state char(2) = ‘%’
AS
BEGIN
SELECT a.au_lname, a.au_fname, t.title
FROM titles t INNER JOIN
titleauthor ta ON t.title_id = ta.title_id RIGHT OUTER JOIN
authors a ON ta.au_id = a.au_id
RETURN
END
TIP
If you are running these queries to display the procedure code in a query window in
SSMS, you probably need to modify the query results options to have the procedures

display correctly. From the Query menu, select Query Options. Expand the Results item
and select Text. Enter a value up to 8192 for the Maximum Number of Characters
Displayed in Each Column setting and click OK.
You probably also want t o have the resul ts displayed as text rathe r than in the grid. To
make this change, under the Query menu, select the Results To submenu and then
select Results to Text. As a shortcut, you can press Ctrl+T to switch to Results to Text.
You can press Ctrl+D to switch b ack to Resul ts to G rid.
Modifying Stored Procedures
You can modify the text of a stored procedure by using the ALTER PROCEDURE statement.
The syntax for ALTER PROCEDURE is similar to the syntax for CREATE PROCEDURE (see Listing
28.10). Using ALTER PROCEDURE has a couple advantages over dropping and re-creating a
procedure to modify it. The main advantage is that you don’t have to drop the procedure
first to make the change, so it remains available, even if the
ALTER PROCEDURE command
fails due to a syntax or object reference error. The second advantage is that because you
Download from www.wowebook.com
ptg
892
CHAPTER 28 Creating and Managing Stored Procedures
don’t have to drop the procedure, you don’t have to worry about reassigning permissions
to it after modifying it.
LISTING 28.10 Modifying a Stored Procedure by Using ALTER PROCEDURE
ALTER PROCEDURE title_authors @state char(2) = ‘%’
AS
BEGIN
SELECT a.au_lname, a.au_fname, t.title, t.pubdate
FROM titles t
INNER JOIN titleauthor ta ON t.title_id = ta.title_id
RIGHT OUTER JOIN authors a ON ta.au_id = a.au_id
where state like @state

RETURN
END
Viewing and Modifying Stored Procedures with SSMS
You can also use SSMS to create, view, and modify stored procedures.
To edit a stored procedure in SSMS, expand the Programmability folder and then the
Stored Procedures folder, right-click the name of the procedure you want to modify, and
select Modify (see Figure 28.5).
FIGURE 28.5 Modifying stored procedures in SSMS.
Download from www.wowebook.com
ptg
893
Using Input Parameters
28
SSMS then extracts the ALTER PROCEDURE statement for the selected procedure into a new
query window. Here, you can edit the procedure code as needed and then execute the
contents of the query window to modify the procedure. In addition, the Object Browser in
SSMS provides other options for extracting the stored procedure source code. It can gener-
ate code to create, alter, or drop the selected stored procedure. You can script the stored
procedure source code to a new window, to a file, or to the Windows Clipboard by right-
clicking the stored procedure name in the Object Browser and choosing the appropriate
option (see Figure 28.6).
Using Input Parameters
To increase the flexibility of stored procedures and perform more complex processing, you
can pass parameters to procedures. The parameters can be used anywhere that local vari-
ables can be used within the procedure code.
The following example is a stored procedure that requires three parameters:
CREATE PROC myproc
@parm1 int, @parm2 int, @parm3 int
AS
FIGURE 28.6 Extracting stored procedure source code to a new query window.

Download from www.wowebook.com