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