Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105

    Unanswered: Receiving and sending a cursor with(in) a Stored Procedure

    Can someone post some code that shows a Stored Procedure receiving a cursor that it can process - lets say a group of order detail records are received that must be saved along with the single Order header record.

    And, in another example, a SP returns a result set to the calling program. - For example, a particular sale receipt is pulled up on the screen and the order detail is needed.

    Thanks for help on this,

    Peter

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Cursors are a really poor choice in MS-SQL. It would appear that you are trying to use Oracle-like logic in SQL Server, which is a receipe for disaster.

    Can you explain in a bit more detail what you are planning to do? I suspect that there is a much better way to do the job once we understand what you are trying to accomplish.

    -PatP

  3. #3
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105
    Hi Pat:

    Maybe I should have said "result set" or "table" - I'm fairly new with SS.

    Here's more detail:

    A web app has a screen where the user enters order header information - name, address, etc. - this would be the tblOrder - 1 record. Then they enter the order detail - say 6 individual sales items - tblDetail - 6 records. All these entries are done on 1 screen in the web app.

    Upon save, a stored procedure is called that will do 2 things.

    1. It will save the tblOrder record and generate the PK for this record
    2. It will save the 6 records into tblDetail including stuffing the PK from the tblOrder into a FK field within the tblDetail records.

    1 and 2 would probably be wrapped in a transaction in case either fails it could Rollback. Otherwise, Commit.

    The approach within the SP is basically what I'm after. Does that make sense?

    Thanks,

    Peter
    Last edited by Pdiotte; 02-23-05 at 10:35.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Write one stored procedure that saves your order record and returns the generated key value to your interface.

    Write a second stored procedure that that save the detail records, including the order key returned by the first procedure.

    Your interface should save the order, and then loop through the detail records calling an insert for each one.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105
    Blindman:

    I was hoping for some code to show the passing of the detail record set from the view tier to the SP.

    Secondly - I'd rather fail both the Order record creation and Part records creation if either fails.

    Wouldn't it be better to have both processes in the same stored procedure? Otherwise, end up with an order and no detail?

    Is there anything preventing me from doing both in 1 stored procedure? If not, how do I pass the needed data - that's the key to my question.

    Thanks for helping,

    Peter

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The detail recored set is just passed to the stored procedure as a set of parameters defined in the procedure's heading.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105
    Hi - a coded example of the stored procedure would be most helpful to this newbie. I understand that detailed record set is passed as a parameter. My original question would be to see the code of the Stored Procedure handling the receipt and processing of the detailed record set.

    Thanks,

    Peter

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Create Procedure DetailInsert(@OrderID as int, @DetailInfo as varchar(50))
    as
    begin
    insert into DetailTable (OrderID, DetailInfo) values (@OrderID, @DetailInfo)
    end
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105
    So detailInfo is a record set? It looks like a single field. If it is a record set, are the values comma delimited or is it simply a reference to a record set that was established in the calling program?

    This is where I'm confused. I can handle a SP that inserts a single record into a DB. I'm trying to understand a situation where multiple records are submitted at once.

    Thanks,

    peter

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I have no idea what @DetailInfo is. That is up to you. It is a dummy parameter that represents all the values you need to submit to the procedure. Submit each value as a separate parameter.

    Peter, have you even TRIED to look up how to write Stored Procedure in Books Online?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105
    Blindman:

    I have looked up many things with books online. I thought this might be a place to zero in efficiently for a solution. As someone new, I'm struggling. I apologize that I didn't understand the purpose of this board and to have taken valuable time away from you.

    I have numerous stored procedures that work to insert and update single records. I have some stored procedures to query and return a record set. What I haven't had success in is having a stored procedure receive a group of child records along with parent and save both. If either fails, I want to rollback - that's why I thought there would be a benefit to combining the operations into a single SP transaction.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    The answer here is easy.

    This is not Oracle

    You can not do what you are thinking unless you use bcp or bulk insert.

    In either case that means you'd have to create a file, which I don't think is a good idea.

    You need to iterate through your rs and make a stored procedure call for each record set.

    Now you could put all of the data in to 1 string, pass it to a sproc, the "unstring" it in the sproc...but that would be overkill.

    Use 2 sprocs like the blind dude said.

    Pass the id as an output variable from the first sproc.

    In the second sproc use error checking. If anything fails, perform a delete of the initial record.

    Sorry
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can't set up a stored procedure that will recieve a group of records. They can only accept input parameters.

    I recommend that you either change your application design to one that submits new data one record at a time, or look into other options available through your development interface for handling recordset. I'm not much of an interface programmer, so I can't help you with that, but you might check one of the other sections of DB Forums.

    Sorry if I was short with you. Been sick today. I readily answer question such as "How do I write a stored procedure to do X", but I don't answer questions like "How do I write a stored procedure."

    If you have specific questions, please do post them on this forum and I or somebody else will assist you.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105
    Thank you Brett and Blindman. I now understand that the best way to handle is from the interface, not at SQL server.

    I hope you are feeling better Blindman.

    thanks,

    Peter

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sick?

    Don't you listen to anything I say?

    A bottle of tequila will take care of all those germs
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •