Permissions
Before we can create stored procedures, we first need to have the appropriate access to create objects. Remember
our discussion on database objects from Chapter 1? We learned that stored procedures
are considered objects, just as tables and views are. If we want to create stored
procedures, we first need to grant the Developers role the appropriate permission
to do so.
Try It Out – Granting Permission to Create Stored Procedures
1.
In Enterprise Manager, expand the Wrox_Auto_Works database and then click on Roles in the tree view.
2.
Right click on the db_ddladmin role and choose Properties from the popup menu.
3.
Click on Add. We want to select the Developersrole to add to the db_ddladmin role as a User,
and then click the OK button to close
the dialog. Click OK again.
Your login now has the rights to create stored procedures.
The db_ddladmin role will permit you to create, modify, and drop objects in
the Wrox_Auto_Works database.
Stored Procedure Owner Prefixes
When you create a stored procedure
you become the owner of that
stored procedure. Your login becomes the stored procedure prefix that must be used when executing your stored procedure from VB or
SQL Server by other users. You and the database administrator are the only ones who can change your stored procedure
– no other users have the authority to drop or modify your stored procedure.
The figure below shows a user-created stored procedure with the login (in this
case willist) as the owner
of that stored procedure. Notice that User is the type listed in the Type column
of the details pane:
As we explained above, when another user wants to execute
a stored procedure created by us they must specify the appropriate prefix, as
shown in the following code fragment:
objRS.Open "willist.up_SELECT_state_names", objConn, adOpenForwardOnly,
_
adLockReadOnly, adCmdStoredProc
Notice that we have prefixed our stored procedure with the
owner prefix. Also notice that the Options parameter of the Open method
specifies the adCmdStoredProc constant from the CommandTypeEnum enumeration. This lets SQL Server know that we want to execute a stored
procedure.
If you are executing stored procedures created by yourself,
you do not have to specify the owner prefix. Likewise, you do not have to specify
the dbo prefix
for stored procedures created by the database administrator. However, it is
a good idea to prefix all stored procedures with the owner prefix.
This allows you to share your code during development, and quickly identify
who wrote the stored procedure in case changes are needed.
Let's look at the basic syntax of a stored procedure, which is shown below:
CREATE PROCEDURE procedure_name [@parameter_name data_type [OUTPUT] [,…n]] AS
sql statements to go here
procedure_name
represents the name of the stored procedure and can be up to 128 characters
in length.
@parameter_name is the name of the parameter. All parameters names must be prefixed
with the at (@) sign, which signifies
that this is a local variable.
data_type specifies the data type of the parameter.
The optional OUTPUT keyword specifies that this parameter is an output parameter that
will be returned to the caller.
n is a place holder
for other parameters.
We will be covering this syntax in more detail as we progress
through the chapters. Before we go much further let's examine the code for a
typical stored procedure:
CREATE PROCEDURE up_SELECT_state_names AS
SELECT State_ID,
State_Name_VC, State_Abbreviation_CH
FROM State_T
ORDER BY State_Name_VC
The first line specifies that a stored procedure should be
created, and specifies the name that the stored procedure should have (up_select_state_names). Next, we enter our SQL statements to SELECT certain columns from the State_T table. We also order the results from the SELECT statement by the State_Name_VC column. This will list all state names in alphabetical order.
We will cover the details of creating stored procedures in
just a little bit. Before we do that we need to discuss stored procedure parameters
and return values, which we'll do in the next section.