Stored Proc calling Stored Proc. Can it be done?

[ChrisH2]

I have created a stored procedure that removes an order line and updates balances and totals on related tables.

I now want to create a stored procedure that removes an entire order. I was thinking of calling the stored procedure that removes the line for each line in the order.

Select * from orderlines where orderRef = 123
and then somehow call the sp_RemoveOrderLine(refNo, lineNo)

Can I call a stored procedure from within another stored procedure for each row?

Is it the best way of doing things or should I duplicate the code by creating sp_RemoveOrder with similar code?

I have had a search around and can't seem to find any help.
Ok with T-SQL but fairly new to stored procs.

Thanks in advance.

 

[vongrunt]

> Can I call a stored procedure from within another stored procedure for each row?

That requires looping of some kind - with cursor or temp table or whatever.

> Is it the best way of doing things or should I duplicate the code by creating sp_RemoveOrder with similar code?

Consider tradeoff between code reuse and performance. While looping in SQL is slow, if order lines don't get deleted frequently then I'd say it is acceptable. If performance really matters then duplicate the code.

Technically the best way is to write set-based code that removes as many order lines as you want. Unfortunately, sometimes it is the most complex one. And if you are not sure all deletes will be initiated by stored procedure then trigger is another (last) option.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[ChrisH2]

Hi,

I did original have it in trigger, it took some time to code and worked fine. I found that when an order gets transfer into history (delete from orderline file) it didn't need some on the relevant tables updating. This caused a problem as I was getting tied in knots.

I thought it may be a case of using cursors but I have heard bad things about them. Think I will try the cursor and see how it performs, if its just too slow then I will have to duplicate the code.

Thanks for your help.