PL/SQL procedures to retrieve and output

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

PL/SQL procedures to retrieve and output

Author

Topic

dlmagers10
Starting Member

48 Posts

Posted - 2010-12-03 : 09:20:08

In SQL, I am trying to write a procedure to retrieve and output the book_code, title, type, price for every book whose publisher code is stored in I_AUTHOR_NUM.

This is what I have so far:

create procedure usp_disp_author_code
@bookcde char(4)
@title char(40)
@type char(3)
@publishercde char(3)
@price decimal(6,2)
as

(select publisher_code from inserted)
set book_code = @bookcde
where publisher_code = @publishercde

Tables include:
BOOK ~
BOOK_CODE
TITLE
PUBLISHER_CODE
TYPE
PRICE PAPERBACK

AUTHOR ~
AUTHOR_NUM
AUTHOR_LAST
AUTHOR_FIRST

OK! Not sure on this but I was looking for some one to give me feedback good or bad. Or point me in the right direction.
Thank you, Diana M

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-03 : 09:24:03

THis is an sql server site not oracle
but what's I_AUTHOR_NUM - it isn't in your list of tables

Do you know how to write a select statement joining tables?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dlmagers10
Starting Member

48 Posts

Posted - 2010-12-03 : 09:31:09

Thank you for your feeback. I stated in my first words 'In SQL'. I am not using Orcle.

Yes, I do know how to join with a select statement but I am just learning this so I was not sure with the procedures if you even needed to join them. But looking at my question again. I think I need to do some joining of the table BOOK and AUTHOR. I am really at a lost here. Other than what I have down here. I need to look at this a different way.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-03 : 09:46:20

not pl\sql then - you should probably change the subject.

It doesn't look like you need the publisher table

select
b.book_code, b.title, b.type, b.price
from book b
where publishercode in (selcet publishercode from I_AUTHOR_NUM)

if you ned publisher something like

select
b.book_code, b.title, b.type, b.price
from book b
join publisher p
on book.publishercode = p.publishercode

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources