Professional Web Applications Themes

Newbie - Calling a user-defined function - Microsoft SQL / MS SQL Server

We have a simple bunch of Access queries (procedures/views) similar to the following but cannot get them to work in SQL Server. We are missing something obvious and would be very grateful to know what. We have a database with three simple, obvious and normalized tables called Products, Orders and Order_Lines. We wish to produce a procedure with an output like: Prod. Desc. OrderNo Qty A AAAA 1000 2 B BBBB 1000 5 C CCCC D DDDD 1000 1 E EEEE 1000 4 F FFFF which is a list ALL products with the quantity (if any) of each product ordered ...

  1. #1

    Default Newbie - Calling a user-defined function


    We have a simple bunch of Access queries
    (procedures/views) similar to the following but cannot get
    them to work in SQL Server. We are missing something
    obvious and would be very grateful to know what.

    We have a database with three simple, obvious and
    normalized tables called Products, Orders and Order_Lines.

    We wish to produce a procedure with an output like:

    Prod. Desc. OrderNo Qty
    A AAAA 1000 2
    B BBBB 1000 5
    C CCCC
    D DDDD 1000 1
    E EEEE 1000 4
    F FFFF

    which is a list ALL products with the quantity (if any) of
    each product ordered under a given order number (in this
    case, Order Number 1000). In this example, Order 1000 was
    only for Products A,B,D and E. (Indeed, we do not need the
    column with the order number. I only show it here for
    clarity.)

    Since the Order_Lines table contains many records for many
    different orders and many different products, it is
    essential to FIRST eliminate all the inappropriate rows
    with a filter.

    I have created a user-defined function which will create a table with
    the filtered records. Now I use my stored procedure and run the
    user-defined function with a left join to the temporary table as
    follows;

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

    (
    OL_ProductId nvarchar(50),
    OL_Quantity int
    )

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

    CREATE Procedure GetProductDetailsByPGID
    (
    CartID nvarchar(50),
    P_Product_Group_Id int,
    DiscountRate float
    )
    AS

    Select * From CreateOrderLineFilter(CartId)

    SELECT
    Products.ProductID,
    Products.P_Description,
    OL_Quantity,
    OL_ProductID,
    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

    Already posted a message when someone suggested using a user-defined
    function. Have managed to write user-defined funtion but want to call it
    in a stored procedure. Is this syntax correct?

    What I am doing wrong? Any help much appreciated.


    Thank you.


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    John Guest

  2. #2

    Default Re: Newbie - Calling a user-defined function

    This is correct syntax.

    Select * From CreateOrderLineFilter(CartId)
    See following exmaple on northwind database

    CREATE FUNCTION CreateOrderLineFilter(CartID nvarchar(50))
    RETURNS OrderLineFilter TABLE (i int)
    AS
    BEGIN
    INSERT OrderLineFilter
    SELECT orderid from orders
    WHERE employeeid = cartid
    RETURN
    END
    go
    create proc tt as
    begin
    declare x int
    select x=1
    select * from CreateOrderLineFilter(x)
    end
    go
    exec tt
    go

    --
    -Vishal
    "John Geraghty" <co.uk> wrote in message
    news:#phx.gbl... 


    Vishal Guest

  3. #3

    Default Re: Newbie - Calling a user-defined function

    Hello John !

    Try to help us a little bit. Send us the error, that we needn´t go through
    your code to yse it.

    Jens Süßmeyer.


    "John Geraghty" <co.uk> schrieb im Newsbeitrag
    news:#phx.gbl... 


    Jens Guest

  4. #4

    Default Re: Newbie - Calling a user-defined function

    The error is that it does not find the ProductID and if I
    remove that it does not find the P_Description so I assume
    that it does not see anything in the second select
    statement( in the procedure CreateOrderLineFilter)
    I am calling the query from a website using ADO.NET when I
    try to attach the returned dataset to a datagrid anything
    in the second select statement:
    SELECT [/ref]

    is not there. I know my syntax is obviously flawed and
    would appreciate any help, especially a link to a good
    tutorial or example

    Thanks for your time
    John
     
    needn´t go through 
    Newsbeitrag [/ref]
    get [/ref]
    Order_Lines. [/ref]
    of [/ref]
    was [/ref]
    the [/ref]
    many [/ref]
    create a table with [/ref]
    run the [/ref]
    table as [/ref]
    (50)) [/ref]
    user-defined [/ref]
    but want to call it [/ref]
    *** 
    >
    >
    >.
    >[/ref]
    John Guest

Similar Threads

  1. calling a user defined java class method .....
    By Toby z in forum PHP Development
    Replies: 0
    Last Post: August 20th, 06:39 AM
  2. User-defined function slows down the view
    By David in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 18th, 09:07 PM
  3. Replies: 7
    Last Post: July 23rd, 10:46 PM

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