Professional Web Applications Themes

Help on a VERY BAD Stored procedure - Microsoft SQL / MS SQL Server

Sorry Email address = net On Fri, 08 Aug 2003 08:42:40 -0400, Jon Spartan <net> wrote:  ...

  1. #1

    Default Re: Help on a VERY BAD Stored procedure

    Sorry

    Email address = net

    On Fri, 08 Aug 2003 08:42:40 -0400, Jon Spartan <net>
    wrote:
     

    Jon Guest

  2. #2

    Default Re: Help on a VERY BAD Stored procedure

    why would anyone bother?

    just curious,
    </wqw>

    "Jon Spartan" <net> wrote in message
    news:com... 


    Vlad Guest

  3. #3

    Default Re: Help on a VERY BAD Stored procedure

    HERE IT IS -

    CREATE PROCEDURE cdr_spRate
    --
    AS
    --
    DECLARE
    xRows int, -- ROWS TO PROCESS
    cRows int, -- ROWS PROCESSED
    DURATIONSEC decimal(18,6), -- DURATION IN SECONDS
    xSellRate decimal(18,6), -- RATE TO USE
    AccountID varchar(24), -- ACCOUNT ID MAY BE BLANK THEN USE BRANDID
    BrandID decimal(21,0), -- BRAND ID = ACCOUNT ID AT TIMES
    RateAmount decimal(18,6), -- FINISHED RATED VALUE
    1STINTERVAL int, -- 1ST INTERVAL TO RATE
    1STBILLRATE decimal(18,6), -- RATE USED FOR 1ST INTRERVAL
    REMAININGINTERVALS int, -- NUMBER OF REMAINING INTERVALS TO RATE
    REMAININGBILL decimal(18,6), -- REMAINING INTRERVALS VALUE AFTER
    BEING RATED
    xDelaySec int, -- GRACE SECONDS ALLOWED FROM
    dspAccounts_InvoiceInfo
    FirstBillSec int, -- FIRST SECONDS TO INVOICE FOR FROM
    dspAccounts_InvoiceInfo
    BillSec int, -- BILL FOR EACH OF THESE SECONDS AFTER
    FirstBillSec FROM dspAccounts_InvoiceInfo
    DUR1 int, -- DUR1 = DURATIONSEC - xDelaySec
    DUR2 int, -- DUR2 = DUR1 - FirstBillSec
    INTERVALS int, -- INTERVALS = DUR2 / BillSec
    FirstRate decimal(18,6),-- FirstRate = CallRate / FirsBillSec
    INTERVALR decimal(18,6),-- INTERVALR = CallRate / BillSec
    INTERVALT decimal(18,6) -- INTERVALT = (DUR2 / BillSec) *
    INTERVALR
    --
    -- UPDATE xBilledCallRetail
    --
    DROP TABLE #xBill

    CREATE TABLE #xBill (
    RecNo int IDENTITY (1, 1) NOT NULL ,
    BillRecNo int NULL,
    DURATIONSEC decimal(18,6) NULL,
    xSellRate decimal(18,6) NULL,
    AccountID varchar(24) NULL,
    BrandID decimal(21,0) NULL,
    RateAmount decimal(18,6) NULL)
    --
    --
    --
    INSERT INTO #xBill
    ( BillRecNo, DURATIONSEC, xSellRate,
    AccountID, BrandID)
    --
    SELECT
    --
    RecNo, xDurSec, xSellRate, AccountID, BrandID
    --
    FROM
    dspCDR_Billable WHERE xBilledCallRetail Is Null
    ORDER BY RecNo DESC
    --
    -- GET ROWS TO PROCESS
    --
    SET xRows = RowCount
    --
    -- SET ROW COUNTER
    --
    SET cRows = 1
    --
    BEGINRATELOOP:
    --
    IF cRows > xRows
    BEGIN
    GOTO ENDRATELOOP
    END
    --
    -- GET VARIABLES TO SEND TO STORED PROCEDURE
    --
    SELECT DURATIONSEC = DURATIONSEC FROM #xBill WHERE RecNo = cRows
    SELECT xSellRate = xSellRate FROM #xBill WHERE RecNo = cRows
    SELECT AccountID = AccountID FROM #xBill WHERE RecNo = cRows
    SELECT BrandID = BrandID FROM #xBill WHERE RecNo = cRows
    SELECT xDelaySec = xDelaySec FROM cdrAccounts_InvoiceInfo WHERE
    BrandID = BrandID
    SELECT FirstBillSec = FirstBillSec FROM cdrAccounts_InvoiceInfo WHERE
    BrandID = BrandID
    SELECT BillSec = BillSec FROM cdrAccounts_InvoiceInfo WHERE BrandID =
    BrandID
    --
    -- CHECK IF THIS IS A BILLABLE CALL
    --
    IF DURATIONSEC < xDelaySec
    BEGIN
    SET RateAmount = 0.00
    GOTO UPDATERATE
    END
    --
    -- CALCULATE FOR 1ST DURATION ONLY
    --
    IF DURATIONSEC BETWEEN xDelaySec AND FirstBillSec
    BEGIN
    SET 1STINTERVAL = FirstBillSec
    SET 1STBILLRATE = ( xSellRate / 60 ) * FirstBillSec
    --
    SET RateAmount = ROUND( 1STBILLRATE, 2)
    GOTO UPDATERATE
    END
    --
    -- MULTIPLE INTERVALS
    --
    SET 1STINTERVAL = FirstBillSec
    SET 1STBILLRATE = ( xSellRate / 60 ) * FirstBillSec
    SET REMAININGINTERVALS = CEILING((DURATIONSEC - FirstBillSec) /
    BillSec )
    SET REMAININGBILL = (( xSellRate / 60 ) * BillSec ) *
    REMAININGINTERVALS
    SET RateAmount = ROUND( (1STBILLRATE + REMAININGBILL) ,2)
    --
    UPDATERATE:
    --
    UPDATE #xBill
    SET RateAmount = RateAmount WHERE RecNo = cRows
    --
    SET cRows = cRows + 1
    --
    GOTO BEGINRATELOOP
    --
    ENDRATELOOP:
    --
    -- DO UPDATE INTO dspCDR_Billable HERE !
    --
    UPDATE dspCDR_Billable
    SET xBilledCallRetail = b.RateAmount
    FROM dspCDR_Billable a INNER JOIN #xBill b
    ON a.RecNo = b.BillRecNo
    --
    DROP TABLE #xBill
    --
    /* END OF PROCEDURE */


    On Fri, 08 Aug 2003 08:42:40 -0400, Jon Spartan <net>
    wrote:
     

    Jon Guest

Similar Threads

  1. Stored Procedure - HELP!
    By ccsimmons in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: August 19th, 11:16 AM
  2. help with a stored procedure
    By Jason Tesser in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 3rd, 05:35 PM
  3. Stored procedure
    By Uri in forum Microsoft SQL / MS SQL Server
    Replies: 13
    Last Post: August 19th, 03:23 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