2

I want to know how to define or declare Internal table in MySQL

I am new to MySQL and i Don't know the syntax

as you can see I create Stored Procedure 

CREATE DEFINER=`root`@`localhost` PROCEDURE `MySP`(
    actioncode VARCHAR(5),
    TNewsID BIGINT
)

BEGIN

IF actioncode = 1 then -- Retrive all from the database --
    select *
    from emp.tbnews;
elseIF actioncode = 2 then -- Retrive all from the database By NewsID --
    select NewsID,NewsSubject,NewsSubjectAR,NewsDetails,NewsDetailsAR,CreatedOn,DisplayOrder,
           AllowDisplay,img  
    from emp.tbnews
    Where NewsID=TNewsID;
elseIF actioncode = 3 then -- fkjskldfjklsdf --
    select NewsID,NewsSubject,NewsSubjectAR,NewsDetails,NewsDetailsAR,CreatedOn,DisplayOrder,
           AllowDisplay,img  
    from emp.tbnews;

 END IF;
 END

What I Really want is to declare Internal table before the IF Statement

in Sql Server I am doing it like this 

declare  @tbTemp table (
 a as int,
 b as char...etc.
)

because i want to put insert statement after

IF actioncode = 1
    Insert into @tbTemp

so please if you know tell me how

best regards for every one.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
HAJJAJ
  • 3,667
  • 14
  • 42
  • 70

2 Answers2

5
create temporary table tmp
(
id int unsigned not null,
name varchar(32) not null
)
engine=memory; -- change engine type if required e.g myisam/innodb

insert into tmp (id, name) select id, name from foo... ;

-- do more work...

select * from tmp order by id;

drop temporary table if exists tmp;

or

create temporary table tmp engine=memory select id, name from foo... ;

-- do more work...

select * from tmp order by id;

drop temporary table if exists tmp;
Jon Black
  • 16,223
  • 5
  • 43
  • 42
0

Do you mean CREATE TEMPORARY TABLE ? It's an in-memory table specific to the connection running the statement so unless you are running persistent connections you have no worries on name conflicts.

chx
  • 11,270
  • 7
  • 55
  • 129
  • Correction: CREATE TEMPORARY TABLE does not denote that the table resides in memory. This is accomplished with the type=MEMORY appendum at the end of the creation statement. If you do not specify a type, the temporary table will be a MyISAM table. The only difference to a normal table will be that when the connection that ran the CREATE TEMPORARY TABLE is closed, the temporary table is collected (it is scoped to the connection). – 0xCAFEBABE Jan 13 '11 at 14:17