[go: up one dir, main page]
Scribd
Upload
60 views1 page

Handout 7 - Lecture Notes

The document discusses creating stored procedures in SQL. It provides examples of creating procedures to return lists of titles or employees for a particular type or project. It also discusses creating a procedure to delete a department and reassign its employees. The document demonstrates transactions by updating a title name in an implicit transaction, which locks the table for other clients until the transaction is committed.

Uploaded by

abdi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
60 views1 page

Handout 7 - Lecture Notes

The document discusses creating stored procedures in SQL. It provides examples of creating procedures to return lists of titles or employees for a particular type or project. It also discusses creating a procedure to delete a department and reassign its employees. The document demonstrates transactions by updating a title name in an implicit transaction, which locks the table for other clients until the transaction is committed.

Uploaded by

abdi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
You are on page 1/ 1

507375766.

doc

1 Stored Procedures

1.1 Create a stored procedure

1.1.1 In pubs db
Create a procedure to return a list of titles for a particular type (NB: if all working in
the same pubs db, make sure they put _xxx where xxx is their network user name,
onto the proc name).

Answer:
create procedure spGetTitlesForType_xxx @type char(12)
as
select title_id, title
from titles
where type = @type
go

Using it:
SpGetTitlesForType_xxx 'business'

1.1.2 In lab exercise db


Create a procedure to get a list of Employees working on a particular project. It
should list Employee names.

Create a procedure to delete a Department and assign all Employees in that


Department to another, specified Department (will need to update Employees first,
then delete – to avoid error if try to delete a Department that has associated
Employees).

2 Transactions
To demonstrate:
On one client, change the Implicit_Transactions setting to ON
Start an implicit transaction to update the name of a title:

update titles set title = 'The Busy Executive''s Database Guide – an update' where title_id = 'BU1032'

If other clients now try to select from the titles table, they should find that the query is
taking some time – because the transaction has a lock on the table.
Commit the transaction on the first client:

Commit tran

Now the select on the other clients should complete.

Page 1 of 1

You might also like