Stored procedures and views
You can see definitions for stored
procedures and views in the demo
databases but you can’t change them.
For views, expand the
Right click on a view and select
For stored procedures, expand the
folder and then the
Right click on a stored procedure and select
If you want to experiment with them and
change them, you will have to generate
and run scripts to recreate the definitions
for tables, views, and stored procedures
in your group database and then import
the data from the base tables.
DO NOT import views using the import
process described previously. It copies
the views but
stores the results as base
The instructions that follow assume
that you have NOT already copied or
imported any data from the database
you are scripting. If you have, delete
all of those tables before following
scripts for tables, views, and
Right click on the source database and
A script wizard appears; click
Click the radio button associated with
Select specific database objects
Select all of the tables, views, and stored
procedures ONLY! DO NOT select
Note: a checkbox for views and stored
procedures will only appear if the
database has views or stored procedures.
A window showing script options
. Click on the
There is an option under
Script USE DATABASE.
Select the radio button associated with
Save to Clipboard.
A summary window appears showing
your selections. If they are all correct
button and the
You have now generated a script
(SQL commands to create tables,
views, and stored procedures) and it’s
on the clipboard.
You now have to run this script. To do
Select your destination database.
Right click and select
Into the query window paste the script
you previously saved on the clipboard.
above the above
the stored procedure tab (the red !)
This will create all the tables, views, and
stored procedures that were in the source
Refresh your connection in the
window and you should be able
to see the tables, views, and stored
You can close the script window without
saving it. You don’t need it anymore.
NOTE: the tables are created but they
have no data. Populating a table with data
is a different process.
At this point you should import data from
the base tables in the source database as
described in a previous
Remember to import ONLY the base
It is difficult to predict the order
in which tables will be imported. It’s
possible that the wizard could try to
import a table containing foreign keys
first. If it does this, it is a violation of a
referential integrity rule.
To be on the safe side you
tables using a
Import ONLY the tables that DO NOT
contain a foreign key.
Then import ONLY the tables that contain a
To see a view definition, expand the
folder, right click on a view, and
You can see the results of the view by
clicking on the
In the tool bar above the
Creating Stored Procedures
Right click on the
in your database and select
You will get a template for writing code.
allows you to
enter parameters to the template.
Or you can just enter your code using
to that in the stored
procedures in the demo databases
To execute the code that
stored procedure, click
execute (also the
in your tool bar.
NOTE: This is NOT executing the stored
Your stored procedure is created
a stored procedure, find the
stored procedure name as above, right
click, and select
Makes changes as necessary and again
When you close the window you’ll be
asked if you want to save changes to a
file. This is not necessary if
you’ve done the previous step.
Testing a Stored Procedure
Start Visual Studio .NET 2010
if it appears.
Right click on
You may or may not see a small window
with the title
Choose Data Source.
do then select
Microsoft SQL Server.
drop down menu,
.NET Framework Data Provider
You should see a window with the title
Microsoft SQL Server
If it doesn’t use the
Change Data Source
and proceed as in the previous slide.
Otherwise proceed to the next step.
for the server name
Select the radio button
Select your group database name.
pane expand the
new connection entry and the
Double click on the stored procedure you
want to run.
Its code should appear in a window.
Right click on the stored procedure name
in the server explorer window and select
A window will appear that allows you to
enter values for parameters that are
passed to the stored procedure.
Enter the parameter information; click
You can verify that it worked by going
back to management studio and viewing
There is a debug ability that allows a user
to step through a stored procedure.
To allow this, student logins MUST be
added to the
Given the level of permissions that
provides, that’s not going to happen.
However, our stored procedures will all
be short so it should not be a major issue.
Expand the folder for the table to which
you want to apply the trigger.
Right click on the
Type in trigger code.
NOTE: There are triggers on the student
(probation) and registration (overload
trigger) tables in the university database.
You can view and copy the code