Stored Procedures

[Bruce A. Julseth]

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.....

[NC]

On May 1, 12:36 pm, "Bruce A. Julseth" <[e-mail]>
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

[Bruce A. Julseth]

"NC" <[e-mail] in message
news:11*********************@q75g2000hsh.googlegro ups.com...

On May 1, 12:36 pm, "Bruce A. Julseth" <[e-mail]>
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..

[NC]

On May 2, 5:48 pm, "Bruce A. Julseth" <[e-mail]>
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

[Bruce A. Julseth]

"NC" <[e-mail] in message
news:11**********************@h2g2000hsg.googlegro ups.com...

On May 2, 5:48 pm, "Bruce A. Julseth" <[e-mail]>
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....