Skip to Content
0

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

Feb 11, 2017 at 09:58 AM

43

avatar image
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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Florian Pfeffer
Feb 11, 2017 at 12:17 PM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

thanks for your answer and the link

0