Professional Web Applications Themes

Newbie: calling a UDF from a stored procedure HELP - Microsoft SQL / MS SQL Server

i have three tables: products,orders and orderlines and I want to make a procedure which will give the quantity of each product ordered under a given order number ( I still want the product to show even if there is no quantity). I need to first eliminate all the inappropriate rows from the orderlines table with a filter. I have created a UDF which I think will create a table with the filtered records(below). I then use my sproc and run the UDF with a left join to the temp table. I am trying to test this from a website ...

  1. #1

    Default Newbie: calling a UDF from a stored procedure HELP

    i have three tables: products,orders and orderlines and I want to make
    a procedure which will give the quantity of each product ordered under
    a given order number ( I still want the product to show even if there
    is no quantity). I need to first eliminate all the inappropriate rows
    from the orderlines table with a filter.
    I have created a UDF which I think will create a table with the
    filtered records(below). I then use my sproc and run the UDF with a
    left join to the temp table.
    I am trying to test this from a website through ADO.NET, and the
    dataset that returns does not have any of the information from the
    second select statement in the sproc (productID,etc...) can you have
    two select statements one after another in the sproc (as I have,
    below)and if not how do I write the sproc correctly to call the UDF
    AND Select the data for the dataset

    CREATE FUNCTION CreateOrderLineFilter(OrderID nvarchar(50))
    RETURNS OrderLineFilter TABLE

    (
    SCF_ProductId nvarchar(50),
    SCF_Quantity int
    )

    AS
    BEGIN
    INSERT OrderLineFilter
    SELECT ProductID, Quantity
    FROM OrderLines
    WHERE OrderID=OrderID
    RETURN
    END



    Stored procedure:-

    CREATE Procedure GetProductDetails
    (
    OrderID nvarchar(50),
    P_Product_Group_Id int,
    )
    AS

    Select * From CreateShoppingCartFilter(CartId)

    SELECT
    Products.ProductID,
    Products.P_Description,
    OL_Quantity,
    OL_ProductID
    Products.P_Qty_Of_Sale,
    Products.P_Product_Group_Id,

    FROM
    Products

    LEFT OUTER JOIN OrderLineFilter ON ProductId =
    OrderLineFilter.OL_ProductId

    WHERE

    Products.P_Product_Group_Id = P_Product_Group_Id

    ORDER BY
    Products.ProductID
    GO

    Yours
    John
    John Guest

  2. #2

    Default Re: Newbie: calling a UDF from a stored procedure HELP

    John,

    Can you post the table DDLs & some sample data to test the code? I see you
    create the UDF CreateOrderLineFilter, but use CreateShoppingCartFilter with
    an unknown parameter in your stored procedure. Is that intentional?

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  3. #3

    Default Re: Newbie: calling a UDF from a stored procedure HELP

    last post fired by mistake

    orderlines
    ----------
    recordID int 4
    OrderID nvarchar 50
    Quantity int 4
    SC_ProductID nvarchar 50 (is ProductID)

    data

    recordID OrderID Quantity SC_ProductID
    10 10 1 563-01
    11 10 2 563-02
    12 10 3 566-01
    13 11 1 456-23
    14 12 2 456-24
    products
    --------
    ProductID nvarchar 50
    P_Product_Group_ID int 4
    P_Description navrchar 50

    data

    ProductID P_Product_Group_ID P_Description
    563-01 4 Bow Single Droplet
    563-02 4 Bow Double Droplet
    566-01 4 Bow Single Spike
    566-02 4 Bow Double Spike
    456-23 5 blue candle
    456-24 7 green candle

    I've realised I don't need the order table

    the OrderID passed in is 10
    the P_Product_Group_Id is 4

    I want it to come up with:

    description quantity
    ----------- --------
    Bow Single Droplet 1
    Bow Double Droplet 2
    Bow Single Spike 3
    Bow Double Spike 0

    sorry for the delay - i had to tow my brothers car
    cheers
     
    the code? I see you 
    CreateShoppingCartFilter with 
    intentional? 
    John Guest

  4. #4

    Default Re: Newbie: calling a UDF from a stored procedure HELP

    Here is the stored procedure:

    CREATE PROCEDURE GetProductDetails
    OrderID NVARCHAR(50),
    P_Product_Group_Id INT
    AS
    SELECT p1.P_Description,
    COALESCE(o1.Quantity, 0)
    FROM Products p1
    LEFT OUTER JOIN orderlines o1
    ON p1.ProductID = o1.sc_ProductID
    AND o1.OrderID = OrderID
    WHERE P_Product_Group_ID = P_Product_Group_Id ;
    GO

    Now the usage:
    EXEC GetProductDetails '10', 4

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  5. #5

    Default Newbie: calling a UDF from a stored procedure HELP

    could i get back to you. I have been trying to work it out
    for 3 days reading books etc and it is now friday late at
    night and I am real tired. I have now learned how to make
    table DLL's but keep making mistakes making up the data. I
    will post them on Monday. Thanks for everything,
    especially on how to post properly as i was putting it off
    for fear of geting it wrong. Cheers
    John
    John Guest

  6. #6

    Default Re: Newbie: calling a UDF from a stored procedure HELP

    It worked! Thank you for all your help.
    John 
    John Guest

Similar Threads

  1. oracle stored procedure calling
    By Raju_bhupesh in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 6th, 07:37 AM
  2. Calling stored procedure from MysQL 5.0
    By ShytKicker in forum Coldfusion Database Access
    Replies: 21
    Last Post: September 7th, 06:36 PM
  3. Calling Stored Procedure Weblink
    By Hai in forum Oracle Server
    Replies: 2
    Last Post: November 29th, 04:32 AM
  4. VB 6.0 calling DB2 7.2 stored procedure
    By Uthuras in forum IBM DB2
    Replies: 2
    Last Post: October 13th, 06:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139