Post reply

Deleting temporary table from asp.net VB

  • August 12, 2012 at 4:47 am

    #262864

    I created a query to pivot some data as below. And that works fine.

    But I delete the temp table at the end of the run.

    Now I need to call this from asp.net code.

    Can anyone please help me with how to do that and how to delete the temp table? Thanks much.

    select [ProductLine],[Regions],

    sum([RevenueValue]) as TotalRevenue

    into #tempPivot

    from [vw_SegmentAnalysis]

    group by [ProductLine],[Sales Segment]

    DECLARE @cols VARCHAR(8000)

    SELECT @cols = STUFF(( SELECT DISTINCT

    '],[' + [Regions]

    FROM #tempPivot AS t2

    ORDER BY '],[' + [Regions]

    FOR XML PATH('')), 1, 2, '') + ']'

    DECLARE @query VARCHAR(8000)

    SET @query = '

    SELECT * FROM #tempPivot

    PIVOT

    (

    sum([TotalRevenue])

    FOR [Regions]

    IN (' + @cols + ')

    )

    AS p '

    EXECUTE(@query)

    DROP TABLE #tempPivot

  • August 12, 2012 at 6:11 am

    #1524835

    I just created an SP to delete the temp table.

  • August 12, 2012 at 6:59 am

    #1524840

    You do not need to drop local temp tables at the end of a session. If you call a stored procedure that creates a temp table, the temp table will automatically drop when the stored procedure ends. In fact, another stored procedure won't see your temp table unless it's called from within the first stored procedure.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply