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?

score 3 · Answer 1 · answered Jun 22 '14 at 11:51

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.

score 1 · Answer 2 · answered Mar 05 '13 at 18:52

1

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

answered Mar 05 '13 at 18:52

Sathy Sannasi

171
4

That means that there are differences between them, so they are not completely equivalent. Do you know where can I found the detailed info about that? – AngocA Mar 07 '13 at 08:51
Sorry, I haven't seen any document that points out the exact difference between these two. My comment was based on practical experience. You will encounter such differences with any two different commands/statements. If your question is pure academic, that you want a full list, I am afraid, I don't know. – Sathy Sannasi Mar 12 '13 at 14:13

score 0 · Answer 3 · answered May 06 '22 at 00:19

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;

score -1 · Answer 4 · answered Mar 05 '13 at 22:11

-1

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;

answered Mar 05 '13 at 22:11

Udo Held

12,314
11
67
93

This does not answer my question. – AngocA Mar 07 '13 at 08:50

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

4 Answers4