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,

PeterCursors 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|||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|||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.|||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|||The detail recored set is just passed to the stored procedure as a set of parameters defined in the procedure's heading.|||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|||Create Procedure DetailInsert(@.OrderID as int, @.DetailInfo as varchar(50))
as
begin
insert into DetailTable (OrderID, DetailInfo) values (@.OrderID, @.DetailInfo)
end|||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|||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?|||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.|||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|||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.|||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|||Sick?

Don't you listen to anything I say?

A bottle of tequila will take care of all those germs|||Inadequate solution.

A bottle of tequila is too large to physically smash the germs, and too small to submerge myself in.

Can you suggest something more scalable?|||Inadequate solution.

A bottle of tequila is too large to physically smash the germs, and too small to submerge myself in.

Can you suggest something more scalable?Have you ever tried using a bottle of Tequila to smash the germs? I'd like the video rights, and would make you a very handsome deal.

Scalable? How about many cases of Tequila?

-PatP