Example for stored procedure with output variables.

[Former Member]

Dear Experts,

I have gone through lot of tutorials on the web on how to create a stored procedure. I understand that to use a procedure, syntax is "Call procedure_name(in parameters)". But i have found no appreciable example for stored procedure which have multiple output parameters. Could you please give me an example of procedure with out parameters and how to use(call/handle the out parameters) that? Can the output parameters be a table/view type?

Help would be greatly appreciated.

Thanks in advance

-Adithya

[Abhishek_Hazra · Accepted solution]

Hi

Adithya,

As you have correctly mentioned, you can declare the output parameters of a stored procedure as table type for multi field output.

Below is an example :

CREATE PROCEDURE "ZSP_OUTPUT" (


IN "PRODUCTION_HOUSE" NVARCHAR (4),
OUT "OUTPUT" TABLE
(
 SITE NVARCHAR(4),
 ARTICLE NVARCHAR(18)
)
) 
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER 
	READS SQL DATA AS
BEGIN
 
 OUTPUT = select DISTINCT SITE, ARTICLE from "PRODUCTION_HOUSE" 
 WHERE "PRODUCTION_HOUSE" = :PRODUCTION_HOUSE;
END;

Here I pass a production site number as input & receive the stores related to that production site & all article combination with the store.

Below is the output table from the stored procedure.

Hope this helps 🙂

Best Regards,
Abhi

[Former Member · Accepted solution]

@abhishek.hazra. Thank you so much for a quick and helpful answer. I understood that we can pass multiple outputs using a table. But one small doubt.

1.Will that output table be stored in our schema just like any other user created table or will it be lost after the procedure execution ends?

2.If we have only single output of standard datatype (say varchar/number etc...) from procedure then how to handle it or use it later in our other procedures?

Thanks in Advance

-Adithya

[Abhishek_Hazra · Accepted solution]

Hi Adithya,

1. The output table is valid for the same scope of the script where the SP is being called, just like the output parameters of a function module in ABAP you can reuse it as a local table later within a scope of script where you call the SP with an input parameter & then hold the output in a local table & use the local table further in the script. So, yes the table data is lost after the SP execution & is not stored anywhere unless you write it into a table.

2. You can similarly pass the singular output parameter from an SP by storing the output into a local variable within the same scope of the script & pass the local variable as input to other input enabled stored procedures if needed.

Best Regards,
Abhi