Stored procedures and views

basiliskcanoeΛογισμικό & κατασκευή λογ/κού

2 Νοε 2013 (πριν από 3 χρόνια και 8 μήνες)

84 εμφανίσεις

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.

1.
Import ONLY the tables that DO NOT
contain a foreign key.

2.
Then import ONLY the tables that contain a
foreign key
.

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