Folks,

Ive been trying to clean up a lot of old code that I have inherited
and make it more efficient. In the process I have been spending hours
with my nose buried in the sql reference manuals and here in the
forums learning all I can from you educated types.

My latest problem with multi row inserts was resolved very nicely
where I now know better, but in the process discovered that the same
principle applies to both the SET and VALUES statements with
particular regard to host variables.

In the Stored Procedures I am working on, there are a significant
number of SET statements such as : SET v_1 = 1; and SET v_2 = 'blah';
or SET v_1 = v_2 +1; and so on. From reading the SQL reference manual
I came across being able to combine the SET statements in much the
same way you would VALUES .. INTO or INSERT using multi row.

I figured this would make the code more efficient due to the large
number of SET statements in nested WHILE loops in the code. The manual
shows examples such as perhaps:

   SET v_1 = 2, v_2 = 3, v_4 = 4;
or  SET (v_1, v_2, v_4) = (2, 3, 4);

   VALUES (function1_name(v_date), function2_name(v_num) into (var_1,
var_2)

The same is true for VALUES and indeed I have seen many examples in
this group, however .. I'll be damned if I can get either to work
correctly for me. The multi SETs just give me plain SQL synntax errors
[expecting spaces etc] while the VALUES statements compile but fail in
the sp when called due to scalar results or multiple rows returned.
Ive tried ensuring the data types are all the same in a single
statement but it doesnt help.

Can anyone shed some light on this for me please ? Id appreciate the
input.

Many thanks,
Tim