Solved

Problem with Temp Tables and nested Sprocs/Transactions - 500 points!

Posted on 2004-09-14
13
831 Views
Last Modified: 2008-02-01
Hello

I can't get my temp tables to work today and no-one in my team has a clue either! I have a main SProc which is creating the temp tables which I can then select from in the same SProc (so they are definitely there). I need to use them in another SProc called from the one that created the temp tables (EXEC @status_val=tc_stg_backdated_evts @debug=@debug) and it tells me that the temp tables don't exist. I can select from the main SProc again directly after calling the sub Sproc so what's going on? Happy to provide all SQL if that helps.

Thanks

Katy
0
Comment
Question by:katybagshaw
  • 5
  • 5
  • 3
13 Comments
 
LVL 14

Expert Comment

by:Jan_Franek
ID: 12053320
It should work, I use this quite often. You wrote, that you have problem today, so I suppose it has worked yesterday. Did you change anything ? Did you try to recreate your procedures ? Is the structure of #temp table created in "outer" procedure the same as structure of #temp table used when creating "inner" procedure ?
0
 

Author Comment

by:katybagshaw
ID: 12054023
Thanks for the response. It's never worked - that was just a turn of phrase! They both have the same structure so it can't be that. I've used the same method before, though not at this job. This is the first time I've done it on Sybase 12 though, so I'm wondering if it could be something to do with that.
0
 
LVL 14

Expert Comment

by:Jan_Franek
ID: 12054050
OK, could you post the code ?
0
 

Author Comment

by:katybagshaw
ID: 12054166
Thanks - here it is - I just changed the temp table name by the way but it made no difference...I'm hoping I've just done something silly and having another brain to look at it will make all the difference!


--------------------------------------------------------------------------------------------------

use pbi_db
go

IF OBJECT_ID('dbo.tc_unstaging') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.tc_unstaging
    IF OBJECT_ID('dbo.tc_unstaging') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.tc_unstaging >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.tc_unstaging >>>'
END
go
CREATE PROC dbo.tc_unstaging
(
@debug int = 0,
@source_system varchar(5)
)
AS
BEGIN
/**************************************************************************************************
 ** Author: Katy Bagshaw
 ** Date:   07/07/2004
 ** Desc:   Procedure to get trades from staging table in to live trade
 **         table in the format required by the calculation engine.
 **
 ** History: KB 03/07/2004: Removed deletion of amendment cancellations as no
 **                         longer imported.
 *************************************************************************************************/



--\\\IMPORTANT: Return an error code to tell the calling
--\\\procedure rollback occurred

    DECLARE @message varchar(255)
--    DECLARE @source_system varchar(5)
--    SELECT @source_system = 'GEFFE'

    --\\\------------------------------------------------
    --\\\ Check this is a business day - don't run if it isn't
    declare @nonbusinessday bit
    exec tc_check_run_date @source_system_code = @source_system,@nonbusinessday=@nonbusinessday output
    --\\\------------------------------------------------

    IF @nonbusinessday = 0
    BEGIN
        SELECT @message = convert(varchar(30), getdate()) + 'Run aborted - non business day - tc_unstaging'
        PRINT @message
    END
    ELSE
    BEGIN

        --\\\MAKE TEMP TABLES TO USE LATER
         SELECT
         tt.trade_id,
         tt.trade_date,
         tt.original_trade_id,
         tt.client_account_code,
         tt.table_flag_id,
         tt.site_id
         INTO  #tc_trade
         FROM
         tc_trade tt
         WHERE
         1=2

         SELECT @message = 'Made #tmp_tc_trade'
         PRINT @message

        SELECT
        ttt.trade_date,
        ttt.client_account_code
        INTO #tc_clients
        FROM #tc_trade ttt
        WHERE 1=2

         SELECT @message = 'Made #tc_clients'
         PRINT @message

        SELECT * FROM #tc_trade

        --*******TRANSACTION NESTING********
        DECLARE @status_val int
        DECLARE @trncnt int
   
        SELECT @trncnt = @@trancount --save @@trancount value
   
        IF @trncnt = 0 -- transaction has not begun
        BEGIN
            BEGIN TRAN tc_unstaging --begin tran increments nest level to 1
        END
        ELSE
        BEGIN
            SAVE TRAN tc_unstaging --save tran doesn't increment nest level
        END
        --**********************************

        --\\\****MOVED TO TEST TEMP TABLES****
        --\\\ 7 BACK DATED EVENTS AND CLIENT RECALC
        SELECT * FROM #tc_trade    

        SELECT @message = convert(varchar(30), getdate()) + ' Inserting backdated events and client recalc details - tc_stg_backdated_evts'
        PRINT @message
        EXEC @status_val=tc_stg_backdated_evts @debug=@debug

        SELECT * FROM #tc_trade
   
          --*******TRANSACTION NESTING********
        IF @status_val = 25 --if proc performed rollback
        BEGIN   --determine whether to rollback or continue
            SELECT @message = 'Rolling back transaction'
            PRINT @message
            ROLLBACK TRAN tc_unstaging
            RETURN
        END
        --**********************************
   
   
        --STATIC SETUP
   
        --\\\GOS feed contains additional information to allow us to set
        --\\\up any missing agent accounts, books and custody rules
        IF @source_system = 'GOS'
        BEGIN
            SELECT @message = convert(varchar(30), getdate()) + ' Checking for new static - tc_create_static'
            PRINT @message
            EXEC @status_val=tc_create_static @debug=@debug, @source_system=@source_system
   
              --*******TRANSACTION NESTING********
            IF @status_val = 25 --if proc performed rollback
            BEGIN   --determine whether to rollback or continue
                SELECT @message = 'Rolling back transaction'
                PRINT @message
                ROLLBACK TRAN tc_unstaging
                RETURN
            END
            --**********************************
        END    
       
       
        --PROCESSING
   
        --\\\ 1 GET THE TRADES FROM THE STAGING TABLE
        SELECT @message = convert(varchar(30), getdate()) + ' Getting staging trades - tc_get_staging_trades'
        PRINT @message
        EXEC @status_val=tc_get_staging_trades @debug=@debug, @source_system=@source_system
   
          --*******TRANSACTION NESTING********
        IF @status_val = 25 --if proc performed rollback
        BEGIN   --determine whether to rollback or continue
            SELECT @message = 'Rolling back transaction'
            PRINT @message
            ROLLBACK TRAN tc_unstaging
            RETURN
        END
        --**********************************
   
        --\\\ 2 UPDATE BUY SELL
        SELECT @message = convert(varchar(30), getdate()) + ' Updating Sell - tc_stg_buy_sell'
        PRINT @message
        EXEC @status_val=tc_stg_buy_sell @debug=@debug,@buy_sell_s='S' ,@buy_sell_n='0'
   
          --*******TRANSACTION NESTING********
        IF @status_val = 25 --if proc performed rollback
        BEGIN   --determine whether to rollback or continue
            SELECT @message = 'Rolling back transaction'
            PRINT @message
            ROLLBACK TRAN tc_unstaging
            RETURN
        END
        --**********************************
   
        SELECT @message = convert(varchar(30), getdate()) + ' Updating Buy - tc_stg_buy_sell'
        PRINT @message
        EXEC @status_val=tc_stg_buy_sell @debug=@debug,@buy_sell_s='B' ,@buy_sell_n ='1'
   
          --*******TRANSACTION NESTING********
        IF @status_val = 25 --if proc performed rollback
        BEGIN   --determine whether to rollback or continue
            SELECT @message = 'Rolling back transaction'
            PRINT @message
            ROLLBACK TRAN tc_unstaging
            RETURN
        END
        --**********************************
   
        --\\\ 3 DELETE AMENDMENT CANCELLATIONS - GEFFE ONLY
        /*
        IF @source_system = 'GEFFE'
        BEGIN
            SELECT @message = convert(varchar(30), getdate()) + ' Deleting Amendment Cancellations - tc_stg_delete_cancels'
            PRINT @message
            EXEC @status_val=tc_stg_delete_cancels @debug=@debug
   
              --*******TRANSACTION NESTING********
            IF @status_val = 25 --if proc performed rollback
            BEGIN   --determine whether to rollback or continue
                SELECT @message = 'Rolling back transaction'
                PRINT @message
                ROLLBACK TRAN tc_unstaging
                RETURN
            END
        END
        --**********************************
        */
       
        /*
        --\\\ 4 STORE PENDING IDS - GEFFE ONLY
        IF @source_system = 'GEFFE'
        BEGIN
            SELECT @message = convert(varchar(30), getdate()) + ' Storing Pending Ids - tc_stg_store_pending_ids'
            PRINT @message
            EXEC @status_val=tc_stg_store_pending_ids @debug=@debug
   
              --*******TRANSACTION NESTING********
            IF @status_val = 25 --if proc performed rollback
            BEGIN   --determine whether to rollback or continue
                SELECT @message = 'Rolling back transaction'
                PRINT @message
                ROLLBACK TRAN tc_unstaging
                RETURN
            END
            --**********************************
        END
   
        --\\\ 5 REMOVE CANCEL FLAGS FROM THE PENDING TABLE FOR ANY THAT
        --\\\   HAVE SINCE BEEN UPDATED - GEFFE ONLY
        IF @source_system = 'GEFFE'
        BEGIN
   
            SELECT @message = convert(varchar(30), getdate()) + ' Removing Pending Table Cancel Flags for Amendements - tc_stg_remove_pending_cancels'
            PRINT @message
            EXEC @status_val=tc_stg_remove_pending_cancels @debug=@debug
   
              --*******TRANSACTION NESTING********
            IF @status_val = 25 --if proc performed rollback
            BEGIN   --determine whether to rollback or continue
                SELECT @message = 'Rolling back transaction'
                PRINT @message
                ROLLBACK TRAN tc_unstaging
                RETURN
            END
            --**********************************
        END
   
        --\\\ 5 UPDATE PENDING TABLE FOR ANY THAT
        --\\\   HAVE SINCE BEEN CANCELLED - GEFFE ONLY
        IF @source_system = 'GEFFE'
        BEGIN
            SELECT @message = convert(varchar(30), getdate()) + ' Updating Pending Table for Cancellations - tc_stg_update_pending_cancels'
            PRINT @message
            EXEC @status_val=tc_stg_update_pending_cancels @debug=@debug
   
              --*******TRANSACTION NESTING********
            IF @status_val = 25 --if proc performed rollback
            BEGIN   --determine whether to rollback or continue
                SELECT @message = 'Rolling back transaction'
                PRINT @message
                ROLLBACK TRAN tc_unstaging
                RETURN
            END
            --**********************************
         END
       
        --\\\ 6 REMOVE ENTER ONES FROM THE TRADE TABLE
        --\\\   THAT WE'VE ALREADY GOT THE PENDING FOR - GEFFE ONLY
        IF @source_system = 'GEFFE'
        BEGIN
   
            SELECT @message = convert(varchar(30), getdate()) + ' Removing ENTER trades from Trade table which already came in as PENDING  - tc_stg_delete_enter_trades'
            PRINT @message
            EXEC @status_val=tc_stg_delete_enter_trades @debug=@debug
   
            --*******TRANSACTION NESTING********
            IF @status_val = 25 --if proc performed rollback
            BEGIN   --determine whether to rollback or continue
                SELECT @message = 'Rolling back transaction'
                PRINT @message
                ROLLBACK TRAN tc_unstaging
                RETURN
            END
            --**********************************
        END
        */
       
        /*
        --\\\ 7 BACK DATED EVENTS AND CLIENT RECALC
   
        SELECT @message = convert(varchar(30), getdate()) + ' Inserting backdated events and client recalc details - tc_stg_backdated_evts'
        PRINT @message
        EXEC @status_val=tc_stg_backdated_evts @debug=@debug
   
   
          --*******TRANSACTION NESTING********
        IF @status_val = 25 --if proc performed rollback
        BEGIN   --determine whether to rollback or continue
            SELECT @message = 'Rolling back transaction'
            PRINT @message
            ROLLBACK TRAN tc_unstaging
            RETURN
        END
        --**********************************
        */
       --\\\ 8 COMBINED TRADE STATUS
   
        SELECT @message = convert(varchar(30), getdate()) + ' Updating combined trade status'
        PRINT @message
        EXEC @status_val=tc_stg_combined_trade_sts @debug=@debug
   
          --*******TRANSACTION NESTING********
        IF @status_val = 25 --if proc performed rollback
        BEGIN   --determine whether to rollback or continue
            SELECT @message = 'Rolling back transaction'
            PRINT @message
            ROLLBACK TRAN tc_unstaging
            RETURN
        END
        --**********************************
   
        --\\\ 9 BROKER COUNTERPARTY SPLIT - GOS ONLY
        IF @source_system = 'GOS'
        BEGIN
            SELECT @message = convert(varchar(30), getdate()) + ' Updating broker counterparty split'
            PRINT @message
            EXEC @status_val=tc_stg_broker_ctrpty_split @debug=@debug, @source_system_code = @source_system
   
              --*******TRANSACTION NESTING********
            IF @status_val = 25 --if proc performed rollback
            BEGIN   --determine whether to rollback or continue
                SELECT @message = 'Rolling back transaction'
                PRINT @message
                ROLLBACK TRAN tc_unstaging
                RETURN
            END
            --**********************************
        END
       
        --\\\BLOCK SPLIT - in feed now
        --SELECT @message = convert(varchar(30), getdate()) + ' Updating block split flag'
        --PRINT @message
        --EXEC
        --tc_stg_update_block_split @debug=@debug
   
          --*******TRANSACTION NESTING********
        --IF @status_val = 25 --if proc performed rollback
        --BEGIN   --determine whether to rollback or continue
        --    SELECT @message = 'Rolling back transaction'
        --    PRINT @message
        --    ROLLBACK TRAN tc_unstaging
        --    RETURN
        --END
        --**********************************
   
   
        SELECT @message = convert(varchar(30), getdate()) + ' ****UNSTAGING COMPLETE****'
        --raiserror 50123 'TEST ERROR'
        PRINT @message
       
        --********FINAL TRANSACTION NESTING********    
        IF @@error != 0 -- error condition
        BEGIN
            SELECT @message = 'Rolling back transaction'
            PRINT @message
            ROLLBACK TRAN tc_unstaging --rollback to savepoint or begin tran
            RETURN
        END
        --**********************************
       
        IF @trncnt = 0 --this proc/batch issued begin tran
        BEGIN
            COMMIT TRAN tc_unstaging --commit tran, decrement @@trancount to 0
   
        END
        RETURN --commit not required with save tran
        --*******************************************
    END --@business_day = 1
    RETURN
END    

GO

IF OBJECT_ID('dbo.tc_unstaging') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.tc_unstaging >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.tc_unstaging >>>'
go

GRANT EXECUTE ON tc_unstaging TO PB_USER
GO
GRANT EXECUTE ON tc_unstaging TO bga_group
GO
GRANT EXECUTE ON tc_unstaging TO readonly
GO

-------------------------------------------------------------------------------------------------------

use pbi_db
go

IF OBJECT_ID('dbo.tc_stg_backdated_evts') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.tc_stg_backdated_evts
    IF OBJECT_ID('dbo.tc_stg_backdated_evts') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.tc_stg_backdated_evts >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.tc_stg_backdated_evts >>>'
END
go

CREATE PROC dbo.tc_stg_backdated_evts
(
@debug int = 0

)
AS

BEGIN
/**************************************************************************************************
 ** Author: Katy Bagshaw
 ** Date:   13/07/2004
 ** Desc:   Process backdated events
 **
 ** History:
 *************************************************************************************************/

   
 
    DECLARE @chg_sts_id_unposted numeric(9,0)
    DECLARE @period_start datetime
    DECLARE @trade_id numeric(9,0)
    DECLARE @backdated_evt_type_id_trade numeric(9,0)
    DECLARE @min_trade_date datetime
    DECLARE @today varchar(11)
    DECLARE @site_id int
    DECLARE @table_flag_id numeric(9,0)
    DECLARE @client_account_code varchar(20)
    DECLARE @status_val int
    DECLARE @message varchar(255)
    DECLARE @now datetime
    DECLARE @original_trade_id varchar(30)
    DECLARE @a1_a2_flag int

    IF @@trancount = 0 --this proc will begin transaction
    BEGIN

         --\\\MAKE TEMP TABLES OUTSIDE TRANSACTION TO USE LATER
         --\\\NB THESE ARE MADE IN tc_unstaging normally

         SELECT
         tt.trade_id,
         tt.trade_date,
         tt.original_trade_id,
         tt.client_account_code,
         tt.table_flag_id,
         tt.site_id
         INTO #tc_trade
         FROM
         tc_trade tt
         WHERE
         1=2

        SELECT
        ttt.trade_date,
        ttt.client_account_code
        INTO #tc_clients
        FROM #tc_trade ttt
        WHERE 1=2
   
   
   
    END

    --\\\START TRANSACTIONAL PROCESSING HERE
    --***TRANSACTION NESTING***
    DECLARE @trncnt int
    SELECT @trncnt = @@trancount

    IF @trncnt = 0 --transaction has not begun
        BEGIN TRAN tc_stg_backdated_events --begin tran increments nest level to 1
    ELSE    --already in a transaction
        SAVE TRAN tc_stg_backdated_events  --save tran doesn't increment nest levels
    --**************************
   
    --SELECT
    --tt.trade_id,
    --tt.trade_date,
    --tt.original_trade_id,
    --tt.client_account_code,
    --tt.table_flag_id,
    --tt.site_id
    --INTO #tmp_tc_trade
    --FROM
    --tc_trade tt
    --WHERE
    --tt.original_trade_id IN
    --(SELECT original_trade_id FROM tc_trade
    --WHERE charge_sts_id IS NULL AND tt.trade_date < batch_date)

    --SELECT @chg_sts_id_unposted = charge_sts_id
    --FROM tc_charge_sts
    --WHERE charge_sts_name = 'Unposted'

    --\\\Get anything that has a trade date before today
    --\\\that belongs with today's feed
    --\\\****CHECK IF SHOULD BE BATCH DATE MINUS ONE****
    SELECT @message = 'Inserting into #tc_trade'
    PRINT @message

    SELECT * FROM #tc_trade
   
    INSERT INTO #tc_trade
    (trade_id,
    trade_date,
    original_trade_id,
    client_account_code,
    table_flag_id,
    site_id)
    SELECT
    tt.trade_id,
    tt.trade_date,
    tt.original_trade_id,
    tt.client_account_code,
    tt.table_flag_id,
    tt.site_id
    FROM
    tc_trade tt
    WHERE
    tt.original_trade_id IN
    (SELECT original_trade_id FROM tc_trade
    WHERE charge_sts_id IS NULL AND tt.trade_date < batch_date) --new ones with trade date in past

    IF @@error != 0
    BEGIN
        if @debug = 1
        BEGIN
            PRINT 'ERROR IN tc-stg_backdated_events'
        END
        raiserror 60001 'Insertion into #tc_trade failed'
    -- rollback to begin tran
        ROLLBACK TRAN tc_stg_backdated_evts
        RETURN 25 --return error code indicating rollback
    END

    --\\\Then process these to do client recalc
    --\\\Needs to be the minimum trade date for each client.
    SELECT @message = 'Inserting into #tc_clients'
    PRINT @message

    INSERT INTO #tc_clients
    (
    trade_date,
    client_account_code
    )
    SELECT
    MIN(ttt.trade_date),
    ttt.client_account_code
    FROM #tc_trade ttt
    GROUP BY ttt.client_account_code

    IF @@error != 0
    BEGIN
        if @debug = 1
        BEGIN
            PRINT 'ERROR IN tc-stg_backdated_events'
        END
        raiserror 60001 'Insertion into #tc_clients failed'
    -- rollback to begin tran
        ROLLBACK TRAN tc_stg_backdated_evts
        RETURN 25 --return error code indicating rollback
    END    
   
    SELECT
    @client_account_code=MIN(client_account_code) from #tc_clients
   
    WHILE @client_account_code IS NOT NULL
    BEGIN
        exec @status_val = tc_recalc_client @debug=@debug,
                        @client_account_code=@client_account_code,
                        @min_trade_date=@min_trade_date,
                        @max_trade_date=@now,
                        @a1_a2_flag=@a1_a2_flag,
                        @site_id=@site_id
        IF @@error != 0
            BEGIN
                SELECT @message = 'tc_recalc_client failed for ' + @client_account_code
                raiserror 50003 @message
                -- rollback to begin tran
                ROLLBACK TRAN tc_stg_backdated_evts
                RETURN 25 --return error code indicating rollback
        END
        --*******TRANSACTION NESTING********
        IF @status_val = 25 --if proc performed rollback
        BEGIN   --determine whether to rollback or continue
            SELECT @message = 'Execution of tc_recalc_client failed for ' +@client_account_code
            raiserror 50003 @message
            SELECT @message = 'Rolling back transaction'
            PRINT @message
            ROLLBACK TRAN tc_stg_backdated_evts
            RETURN 25
        END
                    --**********************************
        DELETE #tc_clients WHERE client_account_code = @client_account_code
        SELECT @client_account_code=MIN(client_account_code) from #tc_clients
    END
   

    --\\\And backdated events
    --\\\N.B. Backdated events are trades that should have been charged
    --\\\for in the previous period

    --\\\Get rid of anything that's not a real backdated event
   
    SELECT @backdated_evt_type_id_trade = backdated_evt_type_id
    FROM tc_backdated_evt_type
    WHERE backdated_evt_type_code = 'T'

    SELECT @period_start = datetime_value from tc_system
    WHERE system_table_id = 3 --Previous Month Closed
   
    DELETE #tc_trade
    WHERE trade_date >= @period_start

    SELECT
    @trade_id = MIN(trade_id)
    FROM #tc_trade

       
    SELECT
    @min_trade_date = trade_date,
    @original_trade_id = original_trade_id,
    @table_flag_id = table_flag_id,
    @site_id = site_id
    FROM #tc_trade
    WHERE trade_id = @trade_id
   

    SELECT @now = getdate()
    SELECT @today = convert(varchar(11),@now,106)
   
    WHILE @trade_id IS NOT NULL    
    BEGIN
       
       
        exec @status_val=tc_insert_bde @debug=@debug,
                @backdated_evt_type_id =@backdated_evt_type_id_trade,
                @min_trade_date=@min_trade_date,
                @unstage= 1,
                @original_trade_id =@original_trade_id,
                @today = @today,
                @table_flag_id = @table_flag_id,
                @site_id = @site_id
       
           SELECT @trade_id = MIN(trade_id) FROM #tc_trade
           WHERE trade_id > @trade_id

           --*******TRANSACTION NESTING********
           IF @status_val = 25 --if proc performed rollback
           BEGIN   --determine whether to rollback or continue
               SELECT @message = 'Execution of tc_insert_bde failed for trade_id ' + convert(varchar(100),@trade_id)
               raiserror 50003 @message
               SELECT @message = 'Rolling back transaction'
               PRINT @message
               ROLLBACK TRAN tc_stg_backdated_evts
               RETURN 25
           END
           --**********************************
    END
   


/*    
   
   
   
    --\\\Back Dated Events
    --\\\Ones that have a trade date before the date the Client Recalc
    --\\\table has been set to are considered backdated.
    --\\\Need to find all the trades in an amendment set and use the
    --\\\earliest trade date as the effective date.

    DECLARE @original_trade_id varchar(30)
    DECLARE @min_trade_date datetime
    DECLARE @client_recalc_yesterday datetime
    DECLARE @min_trade_id numeric(9,0)
    DECLARE @client_account_code varchar(20)
    DECLARE @site_id int
    DECLARE @a1_a2_flag int
    DECLARE @backdated_evt_type_id_trade numeric(9,0)
    DECLARE @today varchar(11)
    DECLARE @message varchar(255)
    DECLARE @status_val int
    DECLARE @now datetime
    DECLARE @block_split char(1)
    --DECLARE @site_id int
    DECLARE @table_flag_id numeric(9,0)

    SELECT @now = getdate()
    SELECT @today = convert(varchar(11),@now,106)


    --\\\Different tables can potentially have the same ids
    select @table_flag_id = min(table_flag_id) from tc_trade

    --\\\Get the first trade for today
    SELECT @original_trade_id = MIN(original_trade_id) FROM
    tc_trade WHERE charge_sts_id is Null
    AND table_flag_id = @table_flag_id



    SELECT @backdated_evt_type_id_trade = backdated_evt_type_id
    FROM tc_backdated_evt_type
    WHERE backdated_evt_type_code = 'T'

     --\\\Use client recalc table to define yesterday
    SELECT @client_recalc_yesterday = MIN(from_date)
    FROM tc_client_recalc
       
   
    WHILE @table_flag_id is not Null
    BEGIN
        IF @debug=1
        BEGIN
            SELECT @message = 'Table Flag Id is ' + convert(char(1),@table_flag_id)
            PRINT @message
        END

        WHILE @original_trade_id is not Null
        BEGIN
            IF @debug=1
            BEGIN
                SELECT @message = convert(varchar(100), getdate()) + ' Looking for BDEs for ' + @original_trade_id
                PRINT @message
            END
            --\\\Get earliest date from the trade set
            --\\\This will be the date to backdate from
            --\\\if it's before the Client Recalc 'yesterday'
            SELECT @min_trade_date = MIN(tt.trade_date)
            FROM
            tc_trade tt
            WHERE
            tt.original_trade_id = @original_trade_id --all the ones in a set are linked by the id of the very first trade
            AND table_flag_id = @table_flag_id

            IF @debug = 1
            BEGIN
            SELECT @message = 'Min Trade Date is ' + convert(varchar(100),@min_trade_date)
            PRINT @message
            END

            IF @client_recalc_yesterday is Null
            BEGIN
                SELECT @client_recalc_yesterday = '01-Jan-1800'
       
            END

            IF @debug = 1
            BEGIN
                SELECT @message = 'Yesterday is ' + convert(varchar(100),@client_recalc_yesterday)
                PRINT @message
            END

            --\\\If the smallest trade date in the set is before
            --\\\yesterday then we want to backdate

            IF @min_trade_date < @client_recalc_yesterday
            BEGIN
                --SELECT @message = '    Processing BDEs for ' + @original_trade_id
                --PRINT @message

                --\\\Update the client recalc table for any clients
                --\\\that belong to this trade as client might have been
                --\\\changed!
                --\\\For block split ones we're only interested in the
                --\\\parent level as that's the level we calculate at

                SELECT @min_trade_id = MIN(trade_id) FROM tc_trade
                WHERE original_trade_id = @original_trade_id
                AND table_flag_id = @table_flag_id
                AND ((block_split = 'Y' AND a1_a2_flag = 0)
                    OR block_split = 'N')

                SELECT @client_account_code = ''

                WHILE @min_trade_id is not Null
                BEGIN
                    --\\\Need to update the ones that exist and insert
                    --\\\any new ones




                    SELECT
                    @client_account_code = client_account_code,
                    @site_id = site_id,
                    @a1_a2_flag = a1_a2_flag
                    FROM tc_trade
                    WHERE trade_id = @min_trade_id
                    AND table_flag_id = @table_flag_id

                    exec @status_val = tc_recalc_client @debug=@debug,
                            @client_account_code=@client_account_code,
                            @min_trade_date=@min_trade_date,
                            @max_trade_date=@now,
                            @a1_a2_flag=@a1_a2_flag,
                            @site_id=@site_id
                    IF @@error != 0
                    BEGIN
                        SELECT @message = 'tc_recalc_client failed for ' + @client_account_code
                        raiserror 50003 @message
                        -- rollback to begin tran
                        ROLLBACK TRAN tc_stg_backdated_evts
                        RETURN 25 --return error code indicating rollback
                    END
                    --*******TRANSACTION NESTING********
                    IF @status_val = 25 --if proc performed rollback
                    BEGIN   --determine whether to rollback or continue
                        SELECT @message = 'Execution of tc_recalc_client failed for ' +@client_account_code
                        raiserror 50003 @message
                        SELECT @message = 'Rolling back transaction'
                        PRINT @message
                        ROLLBACK TRAN tc_stg_backdated_evts
                        RETURN 25
                    END
                    --**********************************

                    --\\\Get the next trade in the set
                    select @min_trade_id = min(trade_id) from tc_trade
                    where trade_id > @min_trade_id
                    and original_trade_id = @original_trade_id            
                    AND table_flag_id = @table_flag_id
                    AND ((block_split = 'Y' AND a1_a2_flag = 0)
                    OR block_split = 'N')
                END

                --\\\The back dated event to record is actually
                --\\\the trade(s) that came in today

                select @message = '    Inserting BDE(s) for ' + convert(varchar(20),@original_trade_id)
                print @message

                exec @status_val=tc_insert_bde @debug=@debug,
                               @backdated_evt_type_id =@backdated_evt_type_id_trade,
                               @min_trade_date=@min_trade_date,
                               @unstage= 1,
                               @original_trade_id =@original_trade_id,
                               @today =@today,
                               @table_flag_id = @table_flag_id,
                               @site_id = @site_id

                IF @@error != 0
                BEGIN
                    SELECT @message = 'tc_insert_bde failed for ' + convert(varchar(20),@original_trade_id)
                    raiserror 50003 @message
                    -- rollback to begin tran
                    ROLLBACK TRAN tc_stg_backdated_evts
                    RETURN 25 --return error code indicating rollback
                END
                 --*******TRANSACTION NESTING********
                    IF @status_val = 25 --if proc performed rollback
                    BEGIN   --determine whether to rollback or continue
                        SELECT @message = 'Execution of tc_insert_bde failed for '+ convert(varchar(20),@original_trade_id)
                        raiserror 50003 @message
                        SELECT @message = 'Rolling back transaction'
                        PRINT @message
                        ROLLBACK TRAN tc_stg_backdated_evts
                        RETURN 25
                    END
                --**********************************

            END --Backdated ones
           
            --\\\Set the marker to show this has been processed        
            update tc_trade
            set charge_sts_id = @chg_sts_id_unposted
            --where source_trade_id = @source_trade_id
            where original_trade_id = @original_trade_id
            and charge_sts_id is null
            and table_flag_id = @table_flag_id

           IF @@error != 0
           BEGIN
                SELECT @message = 'Update of trade table charge status failed for ' + convert(varchar(20),@original_trade_id)
                raiserror 50003 @message
            -- rollback to begin tran
                ROLLBACK TRAN tc_stg_backdated_evts
                RETURN 25 --return error code indicating rollback
            END

            --\\\Get the next trade id to look at
            --SELECT @Source_Trade_Id = MIN(Source_Trade_Id) FROM
            select @original_trade_id = min(original_trade_id) from
            tc_trade where charge_sts_id is null
            and original_trade_id > @original_trade_id
            and table_flag_id = @table_flag_id
        END
        --\\\Get the next table id        
        select @table_flag_id = min(table_flag_id) from tc_trade
        WHERE table_flag_id > @table_flag_id

        select @original_trade_id = min(original_trade_id)
            from tc_trade
            where charge_sts_id is null
            and table_flag_id = @table_flag_id

    END --table_flag_id
   
*/    
    if @debug = 1
    BEGIN
        PRINT 'FINISHED tc_stg_backdated_events'
    END


    --raiserror 50123 'TEST ERROR STAGING UPDATE PENDING CANCEL FLAGS'
   
    IF @@error != 0
    BEGIN
        if @debug = 1
        BEGIN
            PRINT 'ERROR IN tc-stg_backdated_events'
        END
        raiserror 50003 'Update of cancel flags in pending table failed'
    -- rollback to begin tran
        ROLLBACK TRAN tc_stg_backdated_evts
        RETURN 25 --return error code indicating rollback
    END
    --***TRANSACTION NESTING***
    IF @@error = 0
    BEGIN
       
        IF @@trancount = 0 --this proc issued begin tran
        BEGIN
            COMMIT TRAN tc_stg_backdated_evts
        END
            -- commit not required with save tran
            RETURN 0
    END
    ELSE
    BEGIN
        -- rollback to begin tran
        ROLLBACK TRAN tc_stg_backdated_evts
        RETURN 25 --return error code indicating rollback
       
    END

--    SELECT @message = convert(varchar(100),getdate()) + ' Finished backdated events'
--    PRINT @message
    --***************************
    RETURN
END
GO

IF OBJECT_ID('dbo.tc_stg_backdated_evts') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.tc_stg_backdated_evts >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.tc_stg_backdated_evts >>>'
go

GRANT EXECUTE ON tc_stg_backdated_evts TO PB_USER
GO
GRANT EXECUTE ON tc_stg_backdated_evts TO bga_group
GO
GRANT EXECUTE ON tc_stg_backdated_evts TO readonly
GO

0
 
LVL 6

Assisted Solution

by:ChrisKing
ChrisKing earned 250 total points
ID: 12054224
in short, the child procedure must declare the same temp table OUTSIDE the procedure definition that that parent procedure use INSIDE the procedure definition
eg

CREATE PROC parent_proc
AS
    CREATE TABLE #fred ( col1 int, col2 datetime, ... )
    -- do something
    EXEC child_proc
    -- do something
    DROP TABLE #fred
    RETURN
GO

CREATE TABLE #fred ( col1 int, col2 datetime, ... )
GO
CREATE PROC child_proc
AS
    -- do something
    RETURN
GO
DROP TABLE #fred
GO
0
 

Author Comment

by:katybagshaw
ID: 12054287
Cool! Let me try that...
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 14

Accepted Solution

by:
Jan_Franek earned 250 total points
ID: 12054431
I see, that you are creating your tables in tc_stg_backdated_evts again in case when @trancount is zero - probably to make it work even when it's not invoked from tc_unstaging. I'm not sure, if it's possibe to achieve this.

To use #temp tables created in tc_unstaging, these tables must exists at the time when tc_stg_backdated_evts is created. And I'm afraid, that if you try to recreate tc_stg_backdated_evts with #temp tables existing in the system, you will get "#temp already exists" error.

I'm not sure if I was clear - you code should look like this:

create proc tc_unstaging
   ...
   SELECT... INTO  #tc_trade
   SELECT... INTO  #tc_clients
   ...
   exec tc_stg_backdated_evts
   ...
go
SELECT... INTO  #tc_trade ...
SELECT... INTO  #tc_clients ...
go
create proc tc_stg_backdated_evts
   ...
go
DROP TABLE #tc_trade
DROP TABLE #tc_clients
go

Do you understand my point ?

If you need tc_stg_backdated_evts to run even without #tc_trade and #tc_clients previously created, you have to create third procedure tc_stg_backdated_evts_sub, which will contain the logic of tc_stg_backdated_evts without creation of these tables and call this tc_stg_backdated_evts_sub procedure from both tc_unstaging (instead of tc_stg_backdated_evts) and tc_stg_backdated_evts as well
0
 
LVL 14

Expert Comment

by:Jan_Franek
ID: 12054460
Yes, my point is the same as ChrisKing's - it took me while to write it :-)
0
 

Author Comment

by:katybagshaw
ID: 12054709
That seems to work  - at least it runs without complaining now. I had a bit where I was trying to get the called SProc to create the tables if it was being run on its own (where @@trancount = 0) - is there a way I can do that as it won't compile now that I'm creating the table outside of the SProc. Ta.
0
 
LVL 14

Expert Comment

by:Jan_Franek
ID: 12054750
See the last paragraph in my answer.
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12054798
well looks like you are going now
0
 

Author Comment

by:katybagshaw
ID: 12054878
Ah yes - sorry! I think I'll pretend I didn't see that though and save myself some work ;) The sub proc is always going to be called from the main one so I don't need worry until someone comes along with other ideas! Thank for all your help you've saved my day!
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 12055048
one word of warning - if you have warm standby replication, then SET REPLICATION OFF for the duration of running in these procedures - replication will fail to create the procedure as the #temp table will not exist when it is run into the standby
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now