Re: SQL - temporary table data inside stored procedure

6 Aug 2013


      Hi

First, fix your typos :)

create table t1(id bigint);
insert into t1 values(23);
CREATE PROCEDURE test1()
BEGIN
   DECLARE TABLE t1tmp (b bigint);
   INSERT INTO t1tmp SELECT id FROM t1;
   DECLARE v_count BIGINT;
   -- returns 0
   SELECT COUNT(*) INTO v_count FROM t1tmp;
END;
call test1();
drop procedure test1();
drop table t1;

Remember that a procedure does not return a value
for that SQL provides functions

CREATE FUNCTION fcn1()
RETURNS bigint
BEGIN
   DECLARE TABLE t1tmp (b bigint);
   INSERT INTO t1tmp SELECT id FROM t1;
   DECLARE v_count BIGINT;
     SET v_count= (select count(*) from t1tmp);
     RETURN v_count;
END;
select fcn1();
drop function fcn1();
drop table t1;

regards, Martin
On 8/6/13 9:38 PM, Radovan Bičiště wrote:
...
CREATE PROCEDURE test1()
BEGIN
   DECLARE TABLE t1tmp (bigint);
   INSERT INTO t1tmp SELECT id FROM t1;
   DECLARE v_cont BIGINT;
   -- returns 0
   SELECT COUNT(*) INTO v_count FROM t1tmp;
END;

Re: SQL - temporary table data inside stored procedure

Martin Kersten