SAP HANA 10. Create an SQLScript Procedure with Imperative Logic

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

SAP HANA SQLScript Imperative Logic IF+ ELSE+ ENDIF, while cycle, For cycle, Case etc.

1, The new file NewFile to create get_product_sale_price.procedure

2, Local Table Type

create typett_procuct_sale_price as table (
ProductIdnvarchar(10),
Categorynvarchar(40),
Price nvarchar(20),
SalePricenvarchar(20)
)


The computer generated alternative text: sAp ANAoevelopment soup. : \SAp learning materials \ ANA\cloudshareRepositol - Workshop\ essionasAp学习资料\日ANA\cloudshareRepositol叭Workshop\, essiona\00\models\get_product_, ale--price.procedureAsAp日ANAstudioFileEditir当\models\get_product, ale--price.procedure, sAp, ANAstudioFileEditir when, Day B - Navigate - SearchprojectRunWindowHelp for the Nan;, O, Fly, A, !Begging, , In order of, ,In projectExpl sentence get-Prodoct--, ", JsAp, ANAR;!? . GApHANA91 e_prICe.pFOHDB SQLConsoleZooedure - carp: horse AP-HANA-EpM DEMO

3, SQLScript

CREATE PROCEDUREget_product_sale_price(
    in productid nvarchar(10),
    out product_sale_pricett_procuct_sale_price
)
LANGUAGESQLSCRIPT
SQLSECURITY INVOKER
READSSQL DATA AS
BEGIN
/*****************************
Writeyour procedure logic
 *****************************/
declare lv_categorynvarchar(40) := null;
declare lv_discountdecimal(15,2) := 0;
lt_product = select"ProductId", "Category", "Price"
from"SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::products"
where"ProductId" = :productid;
select"Category" into lv_category from :lt_product;
if :lv_category ='Notebooks' then
lv_discount := .20;
elseif :lv_category= 'Handhelds' then
lv_discount := .25;
elseif :lv_category= 'Flat screens' then
lv_discount := .30;
elseif :lv_categorylike '%printers%' then
lv_discount := .30;
else
lv_discount := 0.00;
end if;
product_sale_price =
select"ProductId", "Category", "Price",
"Price"-cast(("Price"* :lv_discount) as decimal(15,2)) as "SalePrice"
from :lt_product;
END;


The computer generated alternative text: sAp ANAoevelopment soup. : \SAp learning materials and training ANA\cloudshareRepos Ol - Wor.. Wang OP\Se, sionasAp学习资料训ANA\cloudshareRepos汪Ol叭Wor.晰OP\Se,siona\00, , mode.s\get--product_sale_p「ice.procedureAsAp日ANAstudio三1Ie互dit丝avigates胆rchZroject旦un巡indow旦elp: 肖, 周履自}参, O甲%,, 甲, }乞, 御毛projectSApHANAR, ice.procedure, mode.s\get--product_sale_p

4, Team→Commit& After Activate, the calling code in the SQL Console, the execution results as shown in Fig.

call"_SYS_BIC"."WorkShop.sessiona.00.models/get_product_sale_price"(
productid =>'HT-1000',
product_sale_price=> ?)


The computer generated alternative text: sAp ANAoevelopment c:\Windows\s" soup; tem32\sQLconsoleZ sAp, ANAstudio giant Bu,. FIIeEditNavigatesearchprojectRunwindowHelp, {Shaw, Zhou shoe since} for, O, fly,; China,} not, royal, 'stone project (XPL snacks Philip pHANARl? . SAp"ANAS_In Yiling Bu Feng County, ty"Get-P, Roduct ale-price.procedure tHOB a SQLCon o zone plate,

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Allen at December 03, 2013 - 12:33 AM