SQL - temporary table data inside stored procedure
Hello, I must be doing something horribly wrong. I have a stored procedure where I have temporary table declared, something like: DECLARE TABLE t1tmp (id bigint); Then in the same procedure I do INSERT INTO t1tmp SELECT .....; When I do Count(*) from temporary table in the procedure it returns 0. Fun starts after procedure called. When call is done temporary table is full of data as expected. But it seems the data is nowhere to be found when I look for them inside the procedure. Am I missing something again? Thanks for any hints, Radovan
TEST CASE: 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;
CALL test1; -- actually returns the data SELECT * FROM t1tmp;
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;
Hi, Oh my dear, :) test worked indeed. The issue was in the name clash inside our procedure. During implementation we created real table with the same name as the temporary table that was declared in the procedure. Thanks. Best regards, Radovan
On 08/06/2013 10:02 PM, Martin Kersten wrote:
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;
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
participants (2)
-
Martin Kersten -
Radovan Bičiště