On Jul 17, 1:20 pm, "lenygold via DBMonster.com" <u41482@[EMAIL PROTECTED]
> wrote:
> Here is the SP:
> --#SET TERMINATOR !
> CREATE PROCEDURE execute_immediate (IN in_stmt VARCHAR(1000)
> ,OUT out_sqlcode INTEGER)
> LANGUAGE SQL
> MODIFIES SQL DATA
> BEGIN
> DECLARE sqlcode INTEGER;
> DECLARE EXIT HANDLER FOR sqlexception
> SET out_sqlcode = sqlcode;
> EXECUTE IMMEDIATE in_stmt;
> SET out_sqlcode = sqlcode;
> RETURN;
> END!
>
> I have field HIREDATE char(10)
> SELECT DATE( HIREDATE) FROM EMP_SCREEN_EDIT;
> 1
> ----------
> 12/27/1999
>
> 1 record(s) selected.
> I updated Hiredate using SP - Maked it invalid:
> CALL execute_immediate('UPDATE EMP_SCREEN_EDIT SET HIREDATE =
> ''12/77/1999''',?)
>
> Value of output parameters
> --------------------------
> Parameter Name : OUT_SQLCODE
> Parameter Value : 0
>
> Return Status = 0
>
> After Update:
> SELECT HIREDATE FROM EMP_SCREEN_EDIT
>
> HIREDATE
> ----------
> 12/77/1999
>
> SELECT DATE(HIREDATE) FROM EMP_SCREEN_EDIT
> the string representation of a datetime value is out of range
> sqlcode: -181
>
> Now 2 selects using SP:
>
> CALL execute_immediate('SELECT HIREDATE FROM EMP_SCREEN_EDIT',?)
>
> Value of output parameters
> --------------------------
> Parameter Name : OUT_SQLCODE
> Parameter Value : -84
>
> Return Status = 0
>
> CALL execute_immediate('SELECT DATE(HIREDATE) FROM EMP_SCREEN_EDIT',?)
>
> Value of output parameters
> --------------------------
> Parameter Name : OUT_SQLCODE
> Parameter Value : -84
>
> Return Status = 0
>
> Why Stored Procedure returns in both cases -84
> What is going on???
>
> --
> Message posted viahttp://www.dbmonster.com
EXECUTE IMMEDIATE can't be used for SELECTs. You'll need to use a
CURSOR instead, e.g.,
<stuff here>
DECLARE C_RETURN CURSOR WITH RETURN FOR S_RETURN;--
PREPARE S_RETURN FROM in_stmt;--
OPEN C_RETURN;--
<stuff here>
--Jeff
|
