asked on

Declare a Temporary Table

Hello,

I'm having a problem declaring a temporary table in a stored procedure.

The word TABLE is underlined in RED with a syntax error.
Its the first time I've done this so I dont know whats wrong.

Any ideas?
Thanks
Dan

-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE PROCEDURE `db`.`selectlatestrates` ()
BEGIN
    DECLARE latestwholesaledate DATE;
    DECLARE latestretaildate DATE;
    
    DECLARE resultstable TABLE(
        [fAreaName] VARCHAR(200),
        [fCode] VARCHAR(20),
        [fWholesalePeak] DECIMAL(10,3),
        [fRetailPeak] DECIMAL(10,3),
        [fWholesaleOffPeak] DECIMAL(10,3),
        [fRetailOffPeak] DECIMAL(10,3),
        [fWholesaleConnectionFee] DECIMAL(10,3),
        [fRetailConnectionFee] DECIMAL(10,3),
        [fWholesaleMinimumFee] DECIMAL(10,3),
        [fRetailMinimumFee] DECIMAL(10,3)
    );
    
END$$

Open in new window

ASKER CERTIFIED SOLUTION

Pratima

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anjos

Here is an easy but pretty clear example:

DECLARE @TABLE TABLE(A int, B int, C varchar(50), D int, E datetime, F int)

      INSERT INTO @TABLE(A, B, C, D, F)
      SELECT      CUA_A AS A,
                  B,
                  C,
                  SUM(D) AS D,
                  COUNT(B) AS F
      FROM      CUA_CustomerArticle CUA
                  JOIN ART_Article
                        ON      CUA_B = B
      WHERE      CUA.CUA_A = @A
      Group by B, C, CUA_A

      UPDATE      @TABLE
            SET      E = CUAs.E
      FROM      CUA_CustomerArticle AS CUAs
      WHERE      CUAs.CUA_A = A

      SELECT      *
      FROM      @TABLE
      Order by C

DanJourno

ASKER

anjos,

That doesnt seem the work. The same problem as I already reported.

anjos

and if you use 'CREATE TEMPORARY TABLE' ?

DanJourno

ASKER

Thanks. This was perfect.