on 03-27-2019 8:03 AM
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;
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
86 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.