Skip to Content

Procedure Script which takes a scalar input and table output

/*** Procedure Script ***/

CREATE PROCEDURE “KN227869”.”SQLPCFACT”(IN YR VARCHAR (15), OUT Q_OUT T_TABLE)

LANGUAGE SQLSCRIPT READS SQL DATA WITH RESULT VIEW F_VIEW AS

BEGIN

Q_OUT = SELECT ITEM, SUM(SALES) AS SALES FROM “KN227869″.”FCT”, “KN227869″.”CAL” WHERE “KN227869″.”CAL”.”YEAR”=:YR AND   “KN227869″.”CAL”.”SKEY”=”KN227869″.”FCT”.”SKEY” GROUP BY ITEM;  END;

/*** End of Procedure Script ***/

This below script will CALL the stored procedure.

Input to this stored procedure is scalar parameter value “2012”

Ouput of this stored procedure is a  T_TABLE which is a column table

Before executing the stored procedure T_TABLE has to be created and it should have the same definition as table output that is selected in the procedure 

OUTPUT: Data from the stored procedure will get loaded in table. Only 2012 data will be loaded As we are filtering the source data for the calendar year 2012

This below SQL can be used to send a call statement from any supported client tool to execute the procedure

/*** Call statements for a procedure call ***/

CALL KN227869″.”SQLPC” (‘2012’, T_TABLE) with overview in debug mode;

When using RESULT VIEW in a procedure the tuple output of the procedure is written to RESULT VIEW which can be accessed from a different procedure   using a simple select statement as below

Per my understanding when using result view and calling view using a select statement from another procedure will better perform than executing the entire procedure.

This has a limitation wherein this solution can be implemented only when the output of the procedure should be only one table

/***** Call the output of the procedure from a RESULT VIEW *****/

SELECT * FROM “F_VIEW” WITH PARAMETERS(‘placeholder’= (‘$$yr$$’, ‘2012’));

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply