Newbie stored procedure questions

harpua · October 19th, 2004, 03:33 PM

Question 1. See code below. First off I am new to stored procedures so this may be wishful thinking, but I would appreciate any help on this in understanding it better. What I am trying to do is Insert the item requested into one table and Select it's ID(this part works), then I want to Select the current stock form my Inventory table and return that value through the Response.Write .Parameters("@intOnOrderCount") or does this need to be a Recordset, I am only returning one value? What I don't understand is how to link more than one table up to a stored procedure, I have tried alot of stuff but ended up here.

Question 2. The ultimate thing I want to do is have the user request an item. Then the database will Insert this information. From there Select the requested items inventory and subtract it, if the inventory is below a certain count I want it to return a value so I can send a notification email to me. Is this even possible before I continue on?

'ASP CODE
    Set objCmd = Server.CreateObject("ADODB.Command")
        With objCmd
            .ActiveConnection = strConnect
            .CommandText = "sp_InsertItems"
            .CommandType = adCmdStoredProc
            .Parameters.Append .CreateParameter("@InPut_OrderID", adInteger, adParamInput, 4, NewOrderID)
            .Parameters.Append .CreateParameter("@InPut_OrderID", adInteger, adParamInput, 4, UserID)
            .Parameters.Append .CreateParameter("@InPut_Item_ID", adInteger, adParamInput, 4, ItemID)
            .Parameters.Append .CreateParameter("@InPut_QtyOrdered", adInteger, adParamInput, 4, QtyOrdered)

            .Parameters.Append .CreateParameter("@intItemOrderedID", adInteger, AdParamOutput, , 0)
            .Parameters.Append .CreateParameter("@intOnOrderCount", adInteger, AdParamOutput, , 0)

            .Execute

            'Selected @@Identity--This Works
            Response.Write .Parameters("@intItemOrderedID")

            'Inventory Count from another table, this doesn't work
            Response.Write .Parameters("@intOnOrderCount")

            End With

        Set objCmd = Nothing

'Stored Procedure
Create PROCEDURE sp_AddItemsToOrder
(
    @InPut_OrderID int,
    @InPut_UsrID int,
    @InPut_Item_ID int,
    @InPut_QtyOrdered int,
        @intItemOrderedID int OUTPUT,
        @intOnOrderCount int OUTPUT
)
AS
BEGIN
    INSERT INTO ItemsTable
        (
        Order_ID, Usr_ID, Item_ID, Qty
        )
    VALUES
        (
        @InPut_OrderID, @InPut_Usr_ID, @InPut_Item_ID, @InPut_QtyOrdered
        )
    Declare @ItemOrderedID int
    SELECT @ItemOrderedID = @@IDENTITY
    SET @intItemOrderedID = @ItemOrderedID

    'How do I get this to link up with @intOnOrderCount????
    SELECT OnOrder
    FROM Inventory
    WHERE Item_ID = @InPut_OrderID
END

GO

Thanks In Advance
Mike

happygv · October 19th, 2004, 03:49 PM

Hi Mike,

Code:

'How do I get this to link up with @intOnOrderCount????
    SELECT @intOnOrderCount=OnOrder
    FROM Inventory
    WHERE Item_ID = @InPut_OrderID

Hope this is what you are looking for. If not, explain what does @intOnOrderCount mean here, and how does that link with the data present in the Inventory table, may be with some sample data.

Cheers!

_________________________
- Vijay G
Strive for Perfection

harpua · October 19th, 2004, 04:13 PM

Thanks a million, that worked! One more question, Is this doable through a stored procedure?

Question 2. The ultimate thing I want to do is have the user request an item. Then the database will Insert this information. From there Select the requested items inventory and subtract it, if the inventory is below a certain count I want it to return a value so I can send a notification email to me. Is this even possible before I continue on?

happygv · October 19th, 2004, 04:57 PM

Yes, you can do that. It is possible.

_________________________
- Vijay G
Strive for Perfection