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

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.

"Amir Tohidi" wrote:


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

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)
-----------------------------------------------------------------------------------------------------------------------
"Amir Tohidi"  wrote in message 
news:F74E77A7-6712-4514-ABAD-D9B9BB86E153@microsoft.com...

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

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.

"William (Bill) Vaughn" wrote:


> 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)
> -----------------------------------------------------------------------------------------------------------------------
> "Amir Tohidi"  wrote in message 
> news:F74E77A7-6712-4514-ABAD-D9B9BB86E153@microsoft.com...
> > 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
> >
> > 
>