Transcript Scripts, stored procedures, and views

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 views folder.
Right click on a view and select Design
For stored procedures, expand the
programmability folder and then the
stored procedures folder.
Right click on a stored procedure and select
Modify
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
tables.
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
these instructions.
Generate scripts for tables, views, and
stored procedures
Right click on the source database and
select tasks  Generate Scripts.
A script wizard appears; click Next.
Click the radio button associated with
Select specific database objects
Select all of the tables, views, and stored
procedures ONLY! DO NOT select
anything else.
Note: a checkbox for views and stored
procedures will only appear if the
database has views or stored procedures.
Click Next.
A window showing script options
appears. Click on the Advanced button.
There is an option under General that
reads Script USE DATABASE. Set its
property to False.
Click OK.
Select the radio button associated with
Save to Clipboard.
Click next
A summary window appears showing
your selections. If they are all correct
click the Next button and the Finish
button.
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
this:
Select your destination database.
Right click and select New Query.
Into the query window paste the script
you previously saved on the clipboard.
Click the execute button above the above
the stored procedure tab (the red !)
This will create all the tables, views, and
stored procedures that were in the source
database.
Refresh your connection in the object
explorer window and you should be able
to see the tables, views, and stored
procedures.
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 powerpoint file.
Remember to import ONLY the base
tables.
NOTE: 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 should import
tables using a two step process.
Import ONLY the tables that DO NOT
contain a foreign key.
2. Then import ONLY the tables that contain a
foreign key.
1.
To see a view definition, expand the
views folder, right click on a view, and
select Design.
You can see the results of the view by
clicking on the ! In the tool bar above the
explorer window.
Creating Stored Procedures
Expand the Programmability folder under
your database.
Right click on the Stored Procedure entry
in your database and select New Stored
Procedure.
You will get a template for writing code.
Typing ctrl  shift  M allows you to
enter parameters to the template.
Or you can just enter your code using
logic similar to that in the stored
procedures in the demo databases.
To execute the code that creates the
stored procedure, click execute (also the
red !) in your tool bar.
NOTE: This is NOT executing the stored
procedure.
Your stored procedure is created
To modify a stored procedure, find the
stored procedure name as above, right
click, and select modify.
Makes changes as necessary and again
click execute as before.
When you close the window you’ll be
asked if you want to save changes to a
listed sql file. This is not necessary if
you’ve done the previous step.
Testing a Stored Procedure from Visual
Studio 2010
Start Visual Studio .NET 2010
Close the Start Page if it appears.
Select View  Server Explorer
Right click on data Connection and select
Add Connection.
You may or may not see a small window
with the title Choose Data Source. If you
do then select Microsoft SQL Server.
In the Data Provider drop down menu,
select .NET Framework Data Provider
for SQLServer
Click the Continue button.
You should see a window with the title
Add Connection and the Data source
should specify Microsoft SQL Server
(SQLClient).
If it doesn’t use the Change button to
access the Change Data Source window
and proceed as in the previous slide.
Otherwise proceed to the next step.
Select ICSD for the server name.
Select the radio button Use Windows
Authentication.
Select your group database name.
Click OK
In the Server Explorer pane expand the
new connection entry and the Stored
Procedures folder
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
Execute.
A window will appear that allows you to
enter values for parameters that are
passed to the stored procedure.
Enter the parameter information; click
OK.
You can verify that it worked by going
back to management studio and viewing
the table
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 sysadmin server role.
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.
Creating Triggers
Expand the folder for the table to which
you want to apply the trigger.
Right click on the Triggers folder and
select New Trigger.
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