I want to create XSP to dynamicaly call existing SP by name. I've read manuals and came to the following solution:
/*REPLACE PROCEDURE TestProc(ProcName Varchar(30))
EXTERNAL NAME 'CS!TestProc!c:/TestProc.c!F!TestProc'
PARAMETER STYLE SQL;*/
#define SQL_TEXT Latin_Text
#define MAX_RESULT_SIZE 200;
void TestProc (VARCHAR_LATIN *ProcName,
FNC_CallSP((SQL_TEXT *)ProcName, 0, NULL, NULL, NULL, sqlstate);
This XSP takes only one parameter - the name of the SP which has to be called. SP doesn't take any parameters.
Then I've created SP's which will be called:
replace procedure Short()
replace procedure LongFFFFFFFFFFFFFFFFFFFFFFFFFF()
After this I try to call created procedures using XSP:
- this one executes.
and this one ends with an error: 7504: in UDF/XSP/UDM Samples.TestProc: SQLSTATE 39000:.
Is there logical explanation of this? Why does the error occurs?
And the second question:
It is said in the manual that it's better to use CLI calls rather then FNC_CallSP. Who knows why? In case of using CLI to solve this task is it possible to call SP which takes OUT parameter?
Thank you in advance,
I stumbled on this issue recently. I found that the length of stored procedure name is limited by FNC_MAXNAMELEN value in header file.
You should check your definition in sqltypes_td.h header file. #define FNC_MAXNAMELEN x. x is the stored procedure name length. I think by default it is limited to 30.
Apologize the above solution wont work. This will be fixed in future releases of Teradata DBS. Right now there is restriction of 29 characters for procedure names which are invoked through FNC_CallSP.