Stored Procedures

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Bruce A. Julseth
    #1

    Stored Procedures

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


    Tags: None
    • NC
      #2
      Re: Stored Procedures

      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

        Comment

        • Bruce A. Julseth
          #3
          Re: Stored Procedures


          "NC" <nc@iname.comwr ote in message
          news:1178071238 .832048.11810@q 75g2000hsh.goog legroups.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..


            Comment

            • NC
              #4
              Re: Stored Procedures

              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

                Comment

                • Bruce A. Julseth
                  #5
                  Re: Stored Procedures


                  "NC" <nc@iname.comwr ote in message
                  news:1178310632 .293766.130730@ h2g2000hsg.goog legroups.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....


                    Comment

                    Previous template Next
                    Working...