hi when i am trying to debug the sql script procedure in sap hana development can't see datapreview

[Former Member]

hi when i am trying to debug the sql script procedure in sap hana development can't see datapreview

[Former Member]

CREATE PROCEDURE get_product_sale_price ( in productid nvarchar(10),

out product_sale_price tt_product_sale_price)

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  --DEFAULT SCHEMA <default_schema_name>

  READS SQL DATA AS

BEGIN

declare lv_category nvarchar(40) := null;

declare lv_discount decimal(15,2) := 0;

lt_product = select "PRODUCTID", "CATEGORY", "PRICE"

from "HANAUSER"."PRODUCTSPRO"

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_category like '%printers%' then

lv_discount := .30;

else

lv_discount := 0.00; -- No discount

end if;

product_sale_price =

select "PRODUCTID", "CATEGORY", "PRICE",

"PRICE" - ("PRICE" * :lv_discount) as "SALEPRICE"

from :lt_product;

END;

[patrickbachmann]

Ok looks like you have a procedure not a view so you can now call the procedure from SQL editor or from a calculation type view or from another procedure.  Or you can add a breakpoint to the left side of your procedure by right-clicking it and adding breakpoint and then right click your procedure and choose debug As/Sap hana stored procedure.  ie: I would typically put a dummy line at the bottom of the procedure such as;

debugStopHere := 'y';

and declare at top of your code like this;

debugStopHere NVARCHAR(1) := 'y';

And set my breakpoint there.  Then you can see the results of product_sale_price nicely in the top right variables section of the debug perspective. 

-Patrick

[patrickbachmann]

CREATE PROCEDURE get_product_sale_price ( in productid nvarchar(10),

out product_sale_price tt_product_sale_price)

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  --DEFAULT SCHEMA <default_schema_name>

  READS SQL DATA AS

BEGIN

declare lv_category nvarchar(40) := null;

declare lv_discount decimal(15,2) := 0;

declare debugStopHere NVARCHAR(1) := 'y';

lt_product = select "PRODUCTID", "CATEGORY", "PRICE"

from "HANAUSER"."PRODUCTSPRO"

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_category like '%printers%' then

lv_discount := .30;

else

lv_discount := 0.00; -- No discount

end if;

product_sale_price =

select "PRODUCTID", "CATEGORY", "PRICE",

"PRICE" - ("PRICE" * :lv_discount) as "SALEPRICE"

from :lt_product;

debugStopHere := 'y';

END;

Then add your breakpoint at the final line before END.  You can then see your proc is doing what you want in variables.  I would do this first to see if it's working the way you want then if you want help consuming in a view we can help with that.  It's all in the guide of course but I know it's sometimes a challenge finding it.

-Patrick

[Former Member]

This message was moderated.

[patrickbachmann]

Hi Raja,

Sorry I'm a bit confused.  Are you talking about a procedure or a view?  If it's a procedure there would not be a data preview since it's not an actual view.  You can look at the variables as you are executing the procedure but you have to insert breakpoints in the script.  If it's a calculation view that is SQL script I'm not aware of a way to debug this.  Can you clarify exactly what you are trying to see?

-Patrick