Back to discussion

Insert Into stored procedure problem

Last post was 12 Mar 2008 at 16:22
sql server Hungary
  • 12 years ago

    I need to create a SP that updates a stock transactions table. In this case the transaction is a transfer from one warehouse to another. The transaction table will show both sides of the transfer, i.e 1 transfer will create two records in table like:

    ID         Warehouse   Qty
    1234     001               -1
    1235     002                1

    These transactions must appear consecutively showing from and to etc. However there is a requirement to carry out transfers in bulk, whereby user will select several warehouses to transfer to and only one warehouse to transfer from. This will be for a single stock ref. I need the SP to step thorugh each warehouse and insert a pair of records as above for each warehouse in recordset so table will look like:

    ID         Warehouse   Qty
    1234     001               -1
    1235     002                1
    1236     001               -1
    1236     003                1
    1237     001               -1
    1238     004                1 and so on.

    Thanks for your help

       
    Report abuse
  • 12 years ago

    What does the recordset look like? (ie from; to; quantity)

    I take it that the interface allows the user to specify the quantity to transfer from the "main" warehouse to the associated "satellite" warehouse(?)

    ID         Warehouse   Qty
    1234     001               -10
    1235     002                10
    1236     001               -5
    1236     003                5
    1237     001               -21
    1238     004                21 and so on.

    Joe

    Report abuse
  • 12 years ago

    It also strikes me that this is a stock transactions "details" table. Is there in fact another stock transactions table that simply lists the from; to; quantity and stock ref id as a single entry?

    If so, you could then use a trigger on insert to do the same job. Just a thought.

    Joe

    Report abuse
  • 12 years ago

    Hi Joe

    Sadly not. The stock transactions table lists both sides of the transfer as seperate transactions with each transaction showing the stock ref, warehouse code, date, user, reference (user supplied), transaction type (i.e. T for transfer, or I for issue etc), and quantity. So if qty transferred is 1 then one record in table will show 'from' warehouse code and qty -1, and next record will show 'to' warehouse and qty 1.

    User interface allows user to select from warehouse, stock ref, qty, and then multiple 'to' warehouses. This is to enable a user to bulk transfer a single item and same qty from the same warehouse to multiple selected warehouses (i.e. main warehouse to multiple satellites etc)

    M

    Report abuse
  • 12 years ago

    Ok,

    So what parameters are you envisiging sending to the stored procedure?

    Joe

    Report abuse
  • 12 years ago

    Should have said there is a stock_levels_at_warehouse table as well of course as the stock_header table which of course need to be updated as part of the transaction , but I am okay with that bit.

    Report abuse
  • 12 years ago

    warehouse code, stockref, qty, reference,userid, datetime and transaction type.

    Report abuse
  • 12 years ago

    The warehouse code is the "main" (from) warehouse I take it. What about the list of "to" warehouses? Where do these come from?

    Joe

    Report abuse
  • 12 years ago

    Recordset will be created by user selecting (check box) warehouses from list displayed, i.e form will display all warehouses (except 'from' warehouse) obtained from warehouses table, and check box next to it. Recordset created from this where check box is true.

    M

    Report abuse
  • 12 years ago

    Granted. (I take it that the qty parameter is a value that is to be applied to all "to" warehouses (?)

    Also, how are the warehouse ("to") codes going to be passed to the stored procedure?

    Joe

    Report abuse
  • 12 years ago

    Yes.

    As for warehouse to codes to SP - there lies the rub...

    I figured i need some kind of loop stepping through each record in temp recordset referred to above, whereby the 'to' and 'from' transactions are inserted for each loop.....

    Report abuse
  • 12 years ago

    The loopings not the problem. Its how you intend getting the codes to the procedure ready for processing.

    The SP is expecting a variety of parameters. A "cursor" is then created in sql in order to process the required results created by applying the various parameters to a typical sql select statement in the procedure.

    Where is the temp recordset that you are referring to? Can you post the code that creates it as I get the impression that you're doing this client side rather than server side.

    My take is that you are going to select the warehouses in the GUI and then extract the codes/ids/whatever of these and throw these across to SQL for procesing. That being the case, how do you intend doing this? (together with the transfer quantities)

    We seem to be on different playing fields here. (?)

    Joe

     

    Report abuse

Post a reply

Enter your message below

Sign in or Join us (it's free).

SQL Server forum discussion

SQL Server tutorials

Quick links

Recent activity

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Web Development

Developer Jobs

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

Cancel
“Brevity is the soul of wit” - Shakespeare