Stored Procedure - Error

[Former Member]

Hi ,

I am trying to execute a simple stored procedure and getting the error.

Stored Procedure:

CREATE PROCEDURE ProcWithResultView(IN id INT, OUT o1 CUSTOMER)

LANGUAGE SQLSCRIPT READS SQL DATA

WITH RESULT VIEW Allocation AS

BEGIN

o1 = SELECT * FROM CUSTOMER WHERE CUSTOMER_ID = :id;

END;

Error:

Repository: Encountered an error in repository runtime extension;error preparing statement create procedure "_SYS_BIC"."123267/123" ( ) language SQLSCRIPT sql security definer reads sql data as n /********* Begin Procedure Script ************/ nCREATE PROCEDURE ProcWithResultView(IN id INT, OUT o1 CUSTOMER) nLANGUAGE SQLSCRIPT READS SQL DATA nWITH RESULT VIEW Allocation AS nBEGIN no1 = SELECT * FROM CUSTOMER WHERE CUSTOMER_ID = :id; nEND;n n /********* End Procedure Script ************/ for oid {tenant: , package: 123267, name: 123, type: 2}: sql syntax error: incorrect syntax near "ProcWithResultView": line 3 col 18 (at pos 185) at qp_gram.y:278

Regards,

Nagaraj

[Former Member]

Hi Nagaraj,

Your procedure code ran successfully for me in the SQL editor.

Looking back at your error code:

"Encountered an error in repository runtime extension;error preparing statement create procedure "_SYS_BIC"."123267/123" ( ) language SQLSCRIPT sql sec...."

from the bits highlighted in bold, I think you are trying to create the procedure within your package (by right-clicking ) using the UI option and embedding your above code within it. I don't think this will work - you are essentially trying to create a procedure within another procedure. If you are using the UI for creating procedures - you just need to define the input (id integer) & output parameter fields (in the same order as your fields from Customer table) and just have the following code within it:

o1 = SELECT * FROM CUSTOMER WHERE CUSTOMER_ID = :id;

One thing to bear in the mind is that creating it like the above wouldn't generate a result view (or rather a column view) like it would if you were to run it in SQL editor. If you are keen on creating a procedure using the UI and still generate a column view (result view) at the end of it, then you must create a scripted calculation view instead.

Thanks,

Anooj

[Former Member]

Dear Nagaraj,

as Ravi mentioned if you use select * then all fields will be returned from the database and then your output parameter o1 must be of the same structure like the table. I assume that the outputparameter o1 is a scalar variable that means it only stores for instance the customer ID.

If this is not the case, we should have more information like the table definition of table customer, the data types of the parameters and so on.

Best Regards,
Marcel

[former_member184768]

One suggestion:

replace select * with select col1, col2 etc.. matching with the type CUSTOMER.

Regards,

Ravi