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.

Answer 2

They are, to the best of my knowledge

In some cases, you would do one technique over the other ..

eg. You cannot use WITH UR in SET

SET var1=(selct....from t with ur)

but can do

select a into var1 from t with ur 

Answer 3

When the result of the query is part of a test condition.

For example, when detaching paritions and waiting for the asynchronous process, the following works:

 WHILE (STATUS_PART <> '') DO
  CALL DBMS_LOCK.SLEEP(1);
  SET STATUS_PART = (SELECT STATUS
    FROM SYSCAT.DATAPARTITIONS
    WHERE TABSCHEMA = TABLE_SCHEMA
    AND TABNAME = TABLE_NAME
    AND DATAPARTITIONNAME LIKE 'SQL%' WITH UR);
 END WHILE;

But the following does not:

 WHILE (STATUS_PART <> '') DO
  CALL DBMS_LOCK.SLEEP(1);
  SELECT STATUS INTO STATUS_PART 
    FROM SYSCAT.DATAPARTITIONS
    WHERE TABSCHEMA = TABLE_SCHEMA
    AND TABNAME = TABLE_NAME
    AND DATAPARTITIONNAME LIKE 'SQL%' WITH UR;
 END WHILE;

Answer 4

The SELECT INTO works for SELECT statements.

With SET you can directly assign the outcome of a function, do calculations or assign a different variable. e.g.

SET var = var + 1;
SET var1 = var;