By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,216 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,216 IT Pros & Developers. It's quick & easy.

Stored Procedures

P: n/a
I'm new to having to write stored procedures and would appreciate some good
books I can use to learn. I searched Amazon and really wasn't happy with
what I found, but maybe I really didn't know what to look for. My first case
that I'm looking into is updating a relationship. What I am doing now is
straight SQL!!

I have Table A that has a link field in it pointing to Table B. So, I first
"INSERT" into table B, then do a SELECT on MAX(Key) where Key is an auto
incremented field. I then include the value of "Key" in the data when I do
the INSERT into Table A. Make sense??

- Does this make for writing a stored procedure?
- Is there a better way to do it in SQL?

Thanks.....
May 1 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
NC
On May 1, 12:36 pm, "Bruce A. Julseth" <bruceaj_nosp...@bellsouth.net>
wrote:
>
What I am doing now is straight SQL!!
Unfortunately, there is no such thing as "straight SQL". Every
implementation is different in details...
I have Table A that has a link field in it pointing to
Table B. So, I first "INSERT" into table B, then do
a SELECT on MAX(Key) where Key is an auto
incremented field. I then include the value of "Key"
in the data when I do the INSERT into Table A.
Make sense??
This is a dangerous approach that can backfire in case of concurrent
updates. Instead of SELECT MAX(key), you should be using
LAST_INSERT_ID() (assuming you are running MySQL, of course...)
- Does this make for writing a stored procedure?
If you want to do it as a stored procedure, you can.
- Is there a better way to do it in SQL?
Better in terms of what? Execution time, portability,
maintainability, something else?

Cheers,
NC

May 2 '07 #2

P: n/a

"NC" <nc@iname.comwrote in message
news:11*********************@q75g2000hsh.googlegro ups.com...
On May 1, 12:36 pm, "Bruce A. Julseth" <bruceaj_nosp...@bellsouth.net>
wrote:
>>
What I am doing now is straight SQL!!

Unfortunately, there is no such thing as "straight SQL". Every
implementation is different in details...
>I have Table A that has a link field in it pointing to
Table B. So, I first "INSERT" into table B, then do
a SELECT on MAX(Key) where Key is an auto
incremented field. I then include the value of "Key"
in the data when I do the INSERT into Table A.
Make sense??

This is a dangerous approach that can backfire in case of concurrent
updates. Instead of SELECT MAX(key), you should be using
LAST_INSERT_ID() (assuming you are running MySQL, of course...)
>- Does this make for writing a stored procedure?

If you want to do it as a stored procedure, you can.
>- Is there a better way to do it in SQL?

Better in terms of what? Execution time, portability,
maintainability, something else?

Cheers,
NC
Thanks for the suggestions. I will convert to LAST_INSERT_ID(). Didn't know
about it...

I would like to do it as a stored procedure because I think it's pretty
straight forward. However, I don't know how to write stored procedures. Any
suggestions, or books, I can learn from??

Thanks again..
May 3 '07 #3

P: n/a
NC
On May 2, 5:48 pm, "Bruce A. Julseth" <bruceaj_nosp...@bellsouth.net>
wrote:
>
I would like to do it as a stored procedure because I think it's
pretty straight forward. However, I don't know how to write
stored procedures. Any suggestions, or books, I can learn
from??
Take a look at the documentation first:

http://dev.mysql.com/doc/refman/5.0/...rocedures.html

Cheers,
NC

May 4 '07 #4

P: n/a

"NC" <nc@iname.comwrote in message
news:11**********************@h2g2000hsg.googlegro ups.com...
On May 2, 5:48 pm, "Bruce A. Julseth" <bruceaj_nosp...@bellsouth.net>
wrote:
>>
I would like to do it as a stored procedure because I think it's
pretty straight forward. However, I don't know how to write
stored procedures. Any suggestions, or books, I can learn
from??

Take a look at the documentation first:

http://dev.mysql.com/doc/refman/5.0/...rocedures.html

Cheers,
NC
I will. Thanks....
May 4 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.