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.

See the Syntax, Description, Notes and Examples of RETURN statement.
RETURN - IBM DB2 9.7 for Linux, UNIX, and Windows

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

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.