Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Andover, MA

    Unanswered: Inserting from a function (ugh!!)

    I have a table of invlices which looks something like:

    invoice_id uniqueidentifier,
    customer_id int,
    start_date datetime,
    end_date datetime,
    invoice_open bit

    Each charge I generate is assigned an invoice id, based on the customer_id and date range in which the service took place. If there are no open invoices for the date range, I want to create a new invoice on the fly, so to speak...

    something like:
    INSERT charge_table SELECT [...charge data...], dbo.fn_get_invoice_id(@customer_id, @service_date) AS invoice_id

    The function would either retrieve the existing invoice id (SELECT invoice_id FROM tbl_invoices WHERE customer_id = @customer_id AND @service_date BETWEEN start_date AND end_date AND invoice_open = 1) or insert a new row in tbl_invoices and return the new invoice_id.

    1) Cannot INSERT from a function
    2) Cannot call a stored procedure from a function
    3) Alternative is updating every one of my billing rules (sp_s) with the code to retrieve or create an invoice id would be huge (100+ billing rules)

    Any ideas?


  2. #2
    Join Date
    Aug 2003
    Andover, MA
    Come on. No one has anything to say about this?

  3. #3
    Join Date
    Nov 2002
    Mom said, if I don't have anything nice to say....



    The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function:

    Assignment statements.

    Control-of-Flow statements.

    DECLARE statements defining data variables and cursors that are local to the function.

    SELECT statements containing select lists with expressions that assign values to variables that are local to the function.

    Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.

    INSERT, UPDATE, and DELETE statements modifying table variables local to the function.

    EXECUTE statements calling an extended stored procedures.
    Why not just make it a sproc?

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Aug 2003
    Andover, MA
    Ah, read carefully. It says Extended stored proc (ala C++).

    It's like they are all working against me!!

  5. #5
    Join Date
    Jun 2003
    Provided Answers: 1
    Well, if you don't have referential integrity established between the two tables (which you should...) you could get away with having a trigger on the charges table that adds the new invoice guid if it does not already exist. Sort of putting the cart before the horse, so it would not be my preferred method.

    Otherwise, I think you will need to run this in more than one statement. Have an invoice_id column in your source data that defaults to newid(). Then overwrite this with the uniqueidentifiers of any already existing invoices. Then insert new invoice IDs into the invoice table where the invoice does not already exist.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  6. #6
    Join Date
    Sep 2003
    why not do this?

    insert charge_table exec sp_invoiceinfo @customer_id, @service_date

    inside the sp check for presence. if not found, - insert. then do a select.

  7. #7
    Join Date
    Aug 2003
    Andover, MA
    Okay, I need to be more explicit in what I am doing:

    Here is a billing rule:

    CREATE PROCEDURE [sp_billing_rule_basic_port_usage]

    @tarriff_rule_id INT,
    @start_date DATETIME = NULL,
    @end_date DATETIME = NULL


    INSERT INTO tbl_billing_charges
    ( tarriff_rule_id,
    invoice_description )
    SELECT @tarriff_rule_id AS customer_rule_id,
    tbl_customer_data.customer_id AS customer_id,
    tbl_metered_connections.connection_id AS reference_id,
    tbl_billing_rules.reference_indicator AS reference_indicator,
    tbl_metered_connections.port_start_time AS service_date,
    GETUTCDATE() AS charge_date,
    CAST((tbl_metered_connections.port_duration + (tbl_tarriff_parameters.interval - 1)) / tbl_tarriff_parameters.interval AS INT) AS units,
    tbl_tarriff_parameters.minimum_units AS minimum_units,
    tbl_tarriff_rates.rate AS rate,
    amount = CASE
    WHEN CAST((tbl_metered_connections.port_duration + (tbl_tarriff_parameters.interval - 1)) / tbl_tarriff_parameters.interval AS INT) >= tbl_tarriff_parameters.minimum_units
    THEN CAST((tbl_metered_connections.port_duration + (tbl_tarriff_parameters.interval - 1)) / tbl_tarriff_parameters.interval AS INT) * tbl_tarriff_rates.rate
    tbl_tarriff_parameters.minimum_units * tbl_tarriff_rates.rate
    tbl_tarriff_rules.invoice_description AS invoice_description
    FROM bridge_metering.dbo.tbl_metered_connections tbl_metered_connections
    INNER JOIN bridge_metering.dbo.tbl_metered_conferences tbl_metered_conferences
    ON tbl_metered_connections.conference_id = tbl_metered_conferences.conference_id
    INNER JOIN bridge_metering.dbo.tbl_mcu_list tbl_mcu_list
    ON tbl_metered_conferences.mcu_id = tbl_mcu_list.mcu_id

    INNER JOIN conference_schedules.dbo.tbl_scheduling_conference s tbl_scheduling_conferences
    ON tbl_metered_conferences.scheduling_sys_id = tbl_scheduling_conferences.sys_id
    AND tbl_metered_conferences.scheduling_conf_id = tbl_scheduling_conferences.conf_num
    INNER JOIN conference_schedules.dbo.tbl_scheduling_rooms tbl_scheduling_rooms
    ON tbl_metered_connections.scheduling_sys_id = tbl_scheduling_rooms.sys_id
    AND tbl_metered_connections.scheduling_node_id = tbl_scheduling_rooms.node_id

    INNER JOIN tbl_customer_data
    ON tbl_scheduling_conferences.sys_id = tbl_customer_data.sys_id
    AND tbl_scheduling_conferences.cust_no = tbl_customer_data.cust_no

    INNER JOIN tbl_tarriff_rules
    ON tbl_customer_data.tarriff_id = tbl_tarriff_rules.tarriff_id
    INNER JOIN tbl_billing_rules
    ON tbl_tarriff_rules.rule_id = tbl_billing_rules.rule_id
    INNER JOIN tbl_tarriff_parameters
    ON tbl_tarriff_rules.tarriff_rule_id = tbl_tarriff_parameters.tarriff_rule_id
    INNER JOIN tbl_tarriff_rates
    ON tbl_tarriff_parameters.tarriff_parameter_id = tbl_tarriff_rates.tarriff_parameter_id

    LEFT OUTER JOIN tbl_billing_charges
    ON tbl_metered_connections.connection_id = tbl_billing_charges.reference_id
    AND tbl_tarriff_rules.tarriff_rule_id = tbl_billing_charges.tarriff_rule_id

    WHERE tbl_tarriff_rules.tarriff_rule_id = @tarriff_rule_id
    AND tbl_scheduling_conferences.type_conf <> 'O'
    AND tbl_scheduling_conferences.type_conf <> 'T'
    AND tbl_scheduling_conferences.time_del IS NULL
    AND tbl_metered_connections.service_site = 0
    AND tbl_metered_connections.port_duration > 0
    AND ISNULL(tbl_mcu_list.owner_customer_id,-1) <> tbl_customer_data.customer_id
    AND tbl_billing_charges.charge_id IS NULL
    AND ((@start_date IS NULL OR service_date >= @start_date) AND (@end_date IS NULL OR service_date <= @end_date))

    Each of these rules (I have about 100 of them) are all a single query. I would like to simply join to the tbl_invoices table for the current open invoice_id (using a function, so one is created if it doesn't already exist). You will notice that the date range input params are optional, meaning that the rule can apply to any data from any date range, so I will not nec know what the date range is if I have to call an sp seperately.

    I guess the alternative would be to insert a NULL if there are no open invoices, than after all rules are appied, go back and create invoice id's and update the tbl_billing_charges table.

Posting Permissions

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