Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    8

    Unanswered: DB2 Stored Procedure returns varchar - Error

    Hi,

    I created a DB2 stored procedure as

    Create Procedure DB2INST1.KPI_JOINS_PROC1 (in Calc_Str Varchar(3000), out Output_Query Varchar(3000))


    I did all the processing and At the end I added
    Set Select_final = 'Select '|| sel_full_var || ' from ' || Tab_full_var || ' where ' || Join_Str;

    Set Output_Query = Select_final;

    Return (Output_Query);
    Commit;
    End@


    I'm getting an error : SQL 0058N The datatype of the requrn statement value in an SQL procedure must be integer. I want to return a varchar.

    I searched in all the past topics in this forum and I couldnt get my error cleared. Please help me out.

    thanks,
    Kayal.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    See the Syntax, Description, Notes and Examples of RETURN statement.
    RETURN - IBM DB2 9.7 for Linux, UNIX, and Windows

    Description

    expression
    Specifies a value that is returned from the routine:

    ...
    •If the routine is a procedure, the data type of expression must be INTEGER (SQLSTATE 428F2). A procedure cannot return NULL or a fullselect.
    Last edited by tonkuma; 10-14-11 at 17:26. Reason: Add Notes and Examples

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Wink

    Quote Originally Posted by kayal View Post
    Hi,

    I created a DB2 stored procedure as

    Create Procedure DB2INST1.KPI_JOINS_PROC1 (in Calc_Str Varchar(3000), out Output_Query Varchar(3000))


    I did all the processing and At the end I added
    Set Select_final = 'Select '|| sel_full_var || ' from ' || Tab_full_var || ' where ' || Join_Str;

    Set Output_Query = Select_final;

    Return (Output_Query);
    Commit;
    End@


    I'm getting an error : SQL 0058N The datatype of the requrn statement value in an SQL procedure must be integer. I want to return a varchar.

    I searched in all the past topics in this forum and I couldnt get my error cleared. Please help me out.

    thanks,
    Kayal.
    Your output value returned by out Output_Query.

    "The RETURN statement is used to return from a routine. For SQL functions or methods, it returns the result of the function or method.
    For an SQL procedure, it optionally returns an integer status value."

    Lenny

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The actual problem is that you want to return the query as output parameter. Therefore, you have to set the parameter "out" and not the local variable "Output_Query". Forget about the RETURN.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •