1> /* Advanced SQL Programming Fall 2002*/
2> /* */
3> /* Stored procedures: Examples */
/* (All stored procs from pubs2 followed by two of mine.) */
/* (Examples shown where if the procedure does not change data.) */
4> /* */
5> use pubs2
1> /* */
2> sp_helptext discount_proc
# Lines of Text
---------------
1
(1 row affected)
text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create proc discount_proc as
select discounttype, stor_id, lowqty, highqty, discount
from discounts
(1 row affected)
(return status = 0)
1> /* */
2> discount_proc
discounttype stor_id lowqty highqty
discount
---------------------------------------- ------- ------ -------
--------------------
Initial Customer NULL NULL NULL
10.500000
Volume Discount NULL 100 1000
6.700000
Huge Volume Discount NULL 1001 NULL
10.000000
Customer Discount 8042 NULL NULL
5.000000
(4 rows affected)
(return status = 0)
1> /* */
2> sp_helptext history_proc
# Lines of Text
---------------
2
(1 row affected)
text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create proc history_proc @stor_id char(4)
as
select date,
sales.ord_num,
qty,
salesdetail.title_id,
discount,
price,
total = qty * price * (1 - discount/100)
from sales, salesdetail, titles
where sales.stor_id = @
stor_id
and sales.ord_num = salesdetail.ord_num
and titles.title_id = salesdetail.title_id
order by date desc, sales.ord_num
(2 rows affected)
(return status = 0)
1> /* */
2> /* Try not supplying stor_id: */
3> /* */
4> history_proc
Msg 201, Level 16, State 2:
Procedure 'history_proc':
Procedure history_proc expects parameter @stor_id, which was not supplied.
(return status = -6)
1> /* */
2> history_proc '7131'
date ord_num qty title_id
discount price total
-------------------------- -------------------- ------ --------
-------------------- ------------------------ --------------------
Dec 20 1990 12:00AM Asoap432 50 TC3218
40.000000 20.95 628.500000
Dec 20 1990 12:00AM Asoap432 80 TC7777
40.000000 14.99 719.520000
Dec 20 1990 12:00AM Asoap432 500 BU1111
46.700000 11.95 3184.675000
Sep 8 1987 12:00AM Fsoap867 200 BU1032
46.700000 19.99 2130.934000
Sep 8 1987 12:00AM Fsoap867 350 TC4203
46.700000 11.95 2229.272500
Sep 8 1987 12:00AM Fsoap867 400 MC3021
46.700000 2.99 637.468000
Nov 16 1986 12:00AM Asoap132 35 BU2075
50.500000 2.99 51.801750
Nov 16 1986 12:00AM Asoap132 137 MC3021
57.200000 2.99 175.321640
Nov 16 1986 12:00AM Asoap132 345 BU1032
57.200000 19.99 2951.723400
(9 rows affected)
(return status = 0)
1> /* */
2> sp_helptext byroyalty
# Lines of Text
---------------
1
(1 row affected)
text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create procedure byroyalty @percentage int
as
select au_id from titleauthor
where titleauthor.royaltyper = @percentage
(1 row affected)
(return status = 0)
1> /* */
2> byroyalty 40
au_id
-----------
213-46-8915
267-41-2394
672-71-3249
(3 rows affected)
(return status = 0)
1> /* */
2> sp_helptext title_proc
# Lines of Text
---------------
1
(1 row affected)
text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create proc title_proc ( @title varchar(80))
as
select @title = lower( @title ) + "%"
select title, title_id, price
from titles
where lower(title) like @title
return @@rowcount
(1 row affected)
(return status = 0)
1> /* */
2> title_proc 'You'
title
title_id price
--------------------------------------------------------------------------------
-------- ------------------------
You Can Combat Computer Stress!
BU2075 2.99
(1 row affected)
(return status = 1)
1> /* */
2> sp_helptext titleid_proc
# Lines of Text
---------------
1
(1 row affected)
text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create proc titleid_proc ( @title_id varchar(80))
as
select @title_id = lower( @title_id ) + "%"
select title, title_id, price
from titles
where lower(title_id) like @title_id
return @@rowcount
(1 row affected)
(return status = 0)
1> /* */
2> titleid_proc 'BU'
title
title_id price
--------------------------------------------------------------------------------
-------- ------------------------
The Busy Executive's Database Guide
BU1032 19.99
Cooking with Computers: Surreptitious Balance Sheets
BU1111 11.95
You Can Combat Computer Stress!
BU2075 2.99
Straight Talk About Computers
BU7832 19.99
(4 rows affected)
(return status = 4)
1> /* */
2> sp_helptext storename_proc
# Lines of Text
---------------
2
(1 row affected)
text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create proc storename_proc
@stor_name varchar(40)
as
declare @lowered_name varchar(40)
select @lowered_name = lower(@stor_name) + "%"
select stor_name,
stor_id,
stor_address,
city,
state,
postalcode,
country
from stores
where lower(stor_name) like @lowered_name
return @@rowcount
(2 rows affected)
(return status = 0)
1> /* */
2> storename_proc 'B'
stor_name stor_id
stor_address city state
postalcode country
---------------------------------------- -------
---------------------------------------- -------------------- -----
---------- ------------
Barnum's 7066
567 Pasadena Ave. Tustin CA
92789 USA
Bookbeat 8042
679 Carson St. Portland OR
89076 USA
(2 rows affected)
(return status = 2)
1> /* */
2> sp_helptext storeid_proc
# Lines of Text
---------------
1
(1 row affected)
text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create proc storeid_proc
@stor_id char(4)
as
select stor_name,
stor_id,
stor_address,
city,
state,
postalcode,
country
from stores
where stor_id = @stor_id
return @@rowcount
(1 row affected)
(return status = 0)
1> /* */
2> storeid_proc '5023'
stor_name stor_id
stor_address city state
postalcode country
---------------------------------------- -------
---------------------------------------- -------------------- -----
---------- ------------
Thoreau Reading Discount Chain 5023
20435 Walden Expressway Concord MA
01776 USA
(1 row affected)
(return status = 1)
1> /* */
2> /* NOTE: The next two stored procedures change data in pubs2. */
3> /* Please do not try them out! If you wish to test similar */
4> /* stored procedures, create them in your database space and */
5> /* insert data into your own tables. */
6> /* */
7> sp_helptext insert_sales_proc
# Lines of Text
---------------
1
(1 row affected)
text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create proc insert_sales_proc
@stor_id char(4),
@ordernum varchar(20),
@orderdate varchar(40)
as
insert sales
values(@stor_id, @ordernum, @orderdate)
(1 row affected)
(return status = 0)
1> /* */
2> sp_helptext insert_salesdetail_proc
# Lines of Text
---------------
1
(1 row affected)
text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create proc insert_salesdetail_proc
@stor_id char(4),
@ord_num varchar(20),
@title_id tid,
@qty smallint,
@discount float
as
insert salesdetail
values(@stor_id, @ord_num, @title_id, @qty, @discount)
(1 row affected)
(return status = 0)
1> /* */
2> sp_helptext insert_sales_proc
# Lines of Text
---------------
1
(1 row affected)
text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create proc insert_sales_proc
@stor_id char(4),
@ordernum varchar(20),
@orderdate varchar(40)
as
insert sales
values(@stor_id, @ordernum, @orderdate)
(1 row affected)
(return status = 0)
1> /* */
/* More simple stored procedures
2> use evelyn
1> /* */
1> /* Accept some text and print a response....
2> sp_helptext printme
# Lines of Text
---------------
1
(1 row affected)
text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create procedure printme
@mytext varchar(40)
as
select @mytext as 'This is my message'
print @mytext
(1 row affected)
(return status = 0)
/* Two examples: */
1> /* */
2> printme Hello
This is my message
----------------------------------------
Hello
(1 row affected)
Hello
(return status = 0)
1> /* */
2> printme 'Too many words here!'
This is my message
----------------------------------------
Too many words here!
(1 row affected)
Too many words here!
(return status = 0)
/* */
/* Stored procedure with simple control of flow: */
/* */
2> sp_helptext ack
# Lines of Text
---------------
1
(1 row affected)
text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create procedure ack
@received char(3)
as
if @received like 'n%'
print 'Waiting...'
else
begin
select @received as 'Acknowledgement'
print 'Got it - thanks!'
end
(1 row affected)
(return status = 0)
1> /* */
/* Examples: */
/* */
2> ack no
Waiting...
(return status = 0)
1> /* */
2> ack yes
Acknowledgement
---------------
yes
(1 row affected)
Got it - thanks!
(return status = 0)
1> /* */
2> dump tran evelyn with truncate_only