"SQLSTATE" is not valid in the context where it is

Madhu Chandran · New User Joined: 31 Jan 2006 Posts: 2

Hi gurus,
I am new to DB2 and tried out the following sp in version 7 for windows
but it generated an error
"[IBM][CLI Driver][DB2/LINUX] SQL0206N "SQLSTATE" is not valid in the context where it is used. LINE NUMBER=15. SQLSTATE=42703"

Please help me out with this

CREATE PROCEDURE USER1.cur (INOUT empno int )
RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
-- Declare cursor
DECLARE cr1 CURSOR WITH hold FOR
SELECT employee_id FROM employees
for update of salary;

-- Cursor left open for client application
open cr1;
while (sqlstate='00000') do
fetch cr1 into empno;
update employees set salary=10670 where current of cr1;
end while;

END P1

Thanks and regards,
Madhu

prabs2006 · Active User Joined: 12 Jan 2006 Posts: 104

Hi

Use SQLCODE instead of SQLSTATE

Thanks & Regards
Prabs

Madhu Chandran · New User Joined: 31 Jan 2006 Posts: 2

Thanks for the reply prabs but i tried using sqlcode instead of sqlstate and recieved the same error message replacing sqlstate with sql code thts all.......

prabs2006 · Active User Joined: 12 Jan 2006 Posts: 104

Hi

The reason might be an undefined column,attribute or parameter name
If I smell something else I will let u know.

Thanks & Regards
Prabs

umeshkmrsh · Active User Joined: 21 Sep 2005 Posts: 81 Location: India

Use this code:

------
------
while (sqlcode = 0 ) do
fetch cr1 into empno;

if (sqlcode = 0)
update employees set salary=10670 where current of cr1;

end while;
------
-----

there may be code syntax error.