Sending a set of data to a stored procedure

[Guest]

Hello

I need to write a stored procedure that executes a bunch of bulked and
related(stock) trades. For example, the customer may have requested four
trades to be placed. The trades are related because the money from selling
one stock say needs to fund a new stock purchase.

What is the recommened approach for sending the set of information to my
stored procedure please? I can'r rely on stored proc parameters because the
number of bulk trades varies and can be quite large.

We have thought of two possible approaches:

1. Send a delimited string with special characters in the string signalling
the begining of a new trade instruction. Clearly the stored proc is going to
be complicated because of the string handling it is going to have to do

2. Insert the trade instructions into a temporary table and have the trade
execution stored read the data from it during from its execution

The target database is Sybase.

Thanks
Amir Tohidi

 

[Guest]

Hi

I forgot to mention:

I am not doing an insert at this stage e.g. I am not sending 4 bulk trades
for insertion (yet). The first stage is to have the stored procedure validate
the trade instructions and return data to the middle tier.

In other words, I need to send a set of data to a fetch type stored of
procedure.

 

[William \(Bill\) Vaughn]

This is a fairly common task. Yes, there are a variety of approaches.
Yes, you can pass a delimited list to the procedure and use a function (even
a CLR function in 2K5) to convert the data to an in-memory TABLE and
validate the data. This makes sense if there aren't too many rows (less than
20 or so) but it will work for more.
Another approach (as you suggest) is to create a temporary table. I would
consider creating a local DataTable and exporting that to SQL Server via
SqlBulkcopy (to a #temp) table. This is best if there are a lot of rows
(more than about 20) but it will work for less.

hth
I discuss several ways to do this in my book.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Guest]

Hi Bill

Thanks for your reply.

I personally prefer the temp table approach, but my colleague prefers the
string based approach. My argument is that databses are optimised for working
with tables etc, and not string handling.