Skip to Content

why does declare statement cause results to come out different in SAP HANA stored Procedure

DROP PROCEDURE X;
CREATE PROCEDURE X (OUT U NVARCHAR(12))
LANGUAGE SQLSCRIPT
READS SQL DATA 
AS 
BEGIN
DECLARE U NVARCHAR(12); 
SELECT 'AAA'  INTO U FROM DUMMY;
END ;

CALL X(?) gives ?

but if I dont declare U , then I get the correct result

DROP PROCEDURE X;
CREATE PROCEDURE X (OUT U NVARCHAR(12))
LANGUAGE SQLSCRIPT
READS SQL DATA 
AS 
BEGIN 
SELECT 'AAA'  INTO U FROM DUMMY;
END ;

CALL X(?) gives 'AAA' which is correct

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Feb 11, 2017 at 12:17 PM

    The reason for the behavior is the Variable Scope Nesting support of SQLScript. Due to the different block scopes you have, you have actually two different variables named "U" in the first example. As in the BEGIN ... END block the local declared variable U is used by the SELECT clause, the parameter U results in a null value.

    In your second example you don't have the local variable U declared, so the SELECT clause uses the parameter U, as it is the next valid variable named U within that scope.

    Regards,
    Florian

    Add comment
    10|10000 characters needed characters exceeded