cancel
Showing results for 
Search instead for 
Did you mean: 

declaring, initializing and selecting a parameter in HANA Studio SQL console

former_member606700
Discoverer
0 Kudos

I want to run this code on the SQL console in hana studio without creating a procedure balakumar.viswanathan2

DECLARE FromDate DATE;
SELECT TOP 1 "RefDate" INTO FromDate FROM TestTable;
SELECT FromDate FROM DUMMY;

Accepted Solutions (0)

Answers (5)

Answers (5)

rahuljain257
Participant

Mr @Hussein Hashem

Also please go through DOBEGIN END documentation from SAP HANA - DO WHILE read though description you will understand it. What they mentioned is "Unlike procedures, an anonymous block has no corresponding object created in the catalog."

Now as far your solution goes I have tried with 2 approaches.

1st Approach same like your code & it didn't worked -

//////////////////////////////>>>>>Procedure<<<<///////////////////////////////
CREATE PROCEDURE "TEST_TEST_DELETE_1"( 
    IN FirstName NVARCHAR(100)
   ,IN LastName NVARCHAR(100)
) AS
BEGIN
   SELECT FirstName as "FIRSTNAME", LastName as "LASTNAME" FROM DUMMY;
END;

//////////////////////////////>>>>>QUERY<<<<///////////////////////////////
do begin
DECLARE FirstName NVARCHAR(100);
DECLARE LastName NVARCHAR(100);
FirstName := 'RAHUL';
LastName := ' JAIN';
CALL TEST_TEST_DELETE_1(FirstName,LastName);
end;

2nd Approach tried something different & it worked like the charm.

//////////////////////////////>>>>>CUSTOM TABLE TYPE<<<<///////////////////////////////
CREATE TYPE "TT_TEST" AS TABLE  
( 
"FIRSTNAME" NVARCHAR(100), 
"LASTNAME" NVARCHAR(100)
)

//////////////////////////////>>>>>Procedure<<<<///////////////////////////////
CREATE PROCEDURE "TEST_TEST_DELETE"( 
 IN FirstName NVARCHAR(100)
,IN LastName NVARCHAR(100)
,OUT TE	TT_TEST
) AS
BEGIN
TE = ( SELECT FirstName as "FIRSTNAME", LastName as "LASTNAME" FROM DUMMY );
END;

//////////////////////////////>>>>>QUERY<<<<///////////////////////////////
do begin
DECLARE FirstName NVARCHAR(100);
DECLARE LastName NVARCHAR(100);
FirstName := 'RAHUL';
LastName := ' JAIN';
CALL TEST_TEST_DELETE(FirstName,LastName, :TE);
SELECT * FROM :TE;
end;
kvbalakumar
Active Contributor

Hi,

Try this HANA syntax:

Inside SAP B1

DECLARE FromDate Date;
SELECT TOP 1 "RefDate" INTO FromDate FROM JDT1;
SELECT :FromDate FROM DUMMY;

For HANA SQL console:

DO
BEGIN
	DECLARE FromDate Date;
	SELECT TOP 1 "RefDate" INTO FromDate FROM JDT1;
	SELECT :FromDate FROM DUMMY;
END

Regards.

Bala

rahuljain257
Participant

Greetings,

Please find the below code for your reference - You just need to decorate it using dobegin and end

Do begin
DECLARE FromDate DATE;
SELECT TOP 1 "RefDate" INTO FromDate FROM TestTable;
SELECT FromDate FROM DUMMY;
end
former_member606700
Discoverer
0 Kudos

rahul.jain257 balakumar.viswanathan2 any help on the below question:

please i want to use these variable as parameters in the below procedure.

but the stored procedure is not showing any data knowing that if i execute the stored procedure with static dates it shows data CALL STORED_PROCEDURE('20190101','20191231');

DOBEGIN
DECLARE FromDate Date;
DECLARE ToDate Date;SELECTTOP1CAST("RefDate" AS DATE) INTO FromDate FROM T0;SELECTTOP1CAST("RefDate" AS DATE) INTO ToDate FROM T0 ORDER BY "RefDate" DESC;CALL STORED_PROCEDURE(:FromDate,:ToDate);END
rahuljain257
Participant
0 Kudos

Give me some time & will get back to you.

former_member606700
Discoverer
0 Kudos

balakumar.viswanathan2

please i want to use these variable as parameters in the below procedure.

but the stored procedure is not showing any data knowing that if i execute the stored procedure with static dates it shows data CALL STORED_PROCEDURE('20190101','20191231');

DO
BEGIN
DECLARE FromDate Date;
DECLARE ToDate Date;
SELECT TOP 1 CAST("RefDate" AS DATE) INTO FromDate FROM T0;
SELECT TOP 1 CAST("RefDate" AS DATE) INTO ToDate FROM T0 ORDER BY "RefDate" DESC;
CALL STORED_PROCEDURE(:FromDate,:ToDate);
END