[let99]

hi all

PROCEDURES:

i want to store a select statement into a variable which i want to call later.. how can this be done ???


Varoable1 = select email from person where ....;

insert into movie (email, imdbnr, moviename, duration, genre)
values (Variable1, nimdbnr, nmoviename, nduration, ngenre);

this doesnt work ... anyone has an idea ???

thx

[andrewst]

Originally posted by let99
hi all

PROCEDURES:

i want to store a select statement into a variable which i want to call later.. how can this be done ???


Varoable1 = select email from person where ....;

insert into movie (email, imdbnr, moviename, duration, genre)
values (Variable1, nimdbnr, nmoviename, nduration, ngenre);

this doesnt work ... anyone has an idea ???

thx

What DBMS? If it was Oracle the answer would be:

DECLARE
v_email person.email%TYPE;
BEGIN
select email INTO v_email FROM person where ...;
...
insert into movie (email, imdbnr, moviename, duration, genre)
values (v_email, nimdbnr, nmoviename, nduration, ngenre);
END;

Mind you, what are nimdbnr etc.? They look like variables too!

[dbmadcap]

declare
lvar1 varchar2(50);
begin
select email into lvar1 from person where <condition>;
insert into movie (email, imdbnr, moviename, duration, genre)
values (lvar1, nimdbnr, nmoviename, nduration, ngenre);
end;
/

Do not forget to handle exceptions like no_data_found or too_many_rows.

Originally posted by let99
hi all

PROCEDURES:

i want to store a select statement into a variable which i want to call later.. how can this be done ???


Varoable1 = select email from person where ....;

insert into movie (email, imdbnr, moviename, duration, genre)
values (Variable1, nimdbnr, nmoviename, nduration, ngenre);

this doesnt work ... anyone has an idea ???

thx

[let99]

thank you thank you !!
it was like this:

PROCEDURE add (attr1, attr2, .....);
v_email person.email%TYPE;
BEGIN
select person.email INTO v_email from person,member ....
insert into movie (email, imdbnr, moviename, duration, genre)
values (v_email, nimdbnr, nmoviename, nduration, ngenre);

P.S. it was oracle & plsql

[dbmadcap]

Again, do not forget to handle the exception just in case your select statement fails i.e. returns no rows or more than 1 row.

Originally posted by let99
thank you thank you !!
it was like this:

PROCEDURE add (attr1, attr2, .....);
v_email person.email%TYPE;
BEGIN
select person.email INTO v_email from person,member ....
insert into movie (email, imdbnr, moviename, duration, genre)
values (v_email, nimdbnr, nmoviename, nduration, ngenre);

P.S. it was oracle & plsql