Differences beween 'set' and 'select into' in IBM DB2 SQL PL

Question

When developing in SQL PL, what is the difference between 'set' and 'select into'?

set var = (select count(1) from emp);

select count(1) into var from emp;

Are they completely equivalent? where can I find documention about them?

Answer 1

When issuing a select, and it does not return any value:

• select into throws an exception
• set gets a null value

You can check the difference with these two stored procedures:

Using set:

create or replace procedure test1 (
in name varchar(128)
)
begin
 declare val varchar(128);

 set val = (select schemaname
   from syscat.schemata where schemaname = name);
end @

Using select into

create or replace procedure test2 (
in name varchar(128)
)
begin
 declare val varchar(128);

 select schemaname into val
   from syscat.schemata where schemaname = name;
end @

Call set

$ db2 "call test1('nada')"

  Return Status = 0

Call select into

$ db2 "call test2('nada')"

  Return Status = 0

SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a 
query is an empty table.  SQLSTATE=02000

This is a difference between both of them. When using select into, you have to deal with handlers.