Library sample chapters
Stored Procedures
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.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.
Related articles
Related discussion
-
High-Performance .NET Application Development & Architecture
by Manjot Bawa (0 replies)
-
Help with query and loop
by sara87 (2 replies)
-
Permutations and combinations of multiple return codes
by actuszeus (1 replies)
-
While Inserting Data into Database i.e. MS Access
by satyasahu (1 replies)
-
dtPicker date format query
by konikula (1 replies)
Hi there!
What is meant by this: 3. Don't forget to set a reference to the Microsoft ActiveX Data Objects Library in your project?
Thanks ALOT!
As a new user to the the vb.net, I am curious to know how things work. But when I try some codes written in the book " Beginning Visual Basic .NET Database Programming", I am stuck in a few places.
Firstly, the instruction said "Change the Modifiers property from Assembly to Family." which i cannot find either Assembly or Family in the Properties. So I change Modifier - Friend (assuming that's what the author refer to as assembly) to Public (assuming it's the other word for Family).
Secondly, during creating stored procedures, I only can see Refresh and Properties when trying to create New Stored Procedures.
please advice
This thread is for discussions of Stored Procedures.