Syntax for a SAP HANA Query with Input Parameters

Question

I'm new to SAP HANA SQL syntax and am having trouble defining input parameters. I've done several Google searches and found several different suggestions, none of which have worked. In SQL server, I would define parameters for a query this way:

DECLARE @StartDate DATETIME,
        @EndDate DATETIME;

SELECT EmployeeName,
       EmployeeTitle,
       Salary,
       PositionStartDate,
       PositionEndDate
FROM EmployeeData
WHERE 1 = 1
  AND PositionStartDate = @StartDate
  AND PositionEndDate = @EndDate

How would I write this same query in SAP HANA SQL syntax? Any help or direction that anyone could provide would be greatly appreciated!

Lars Br. · Accepted Answer · 2020-09-26 04:09:48Z

2

The syntax you've shown is MS SQL Servers's T-SQL, the procedural SQL extension, not standard SQL.

SAP HANA's equivalent is SQLScript and the corresponding code snippet would look like this:

DO BEGIN
DECLARE startDate DATE;
DECLARE endDate DATE;
   
   SELECT EmployeeName,
          EmployeeTitle,
          Salary,
          PositionStartDate,
          PositionEndDate
   FROM EmployeeData
   WHERE 1 = 1
     AND PositionStartDate = :startDate
     AND PositionEndDate = :enDate;

END;

answered Sep 26, 2020 at 4:09

Lars Br.

10.5k2 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Matt Mitchell Over a year ago

Thanks, Lars Br....I'll give that a try! I appreciate the assist!

Matt Mitchell Over a year ago

I declared the variables as you suggested, but when I run the query get the following error message: Syntax error or access violation; 257 sql syntax error: incorrect syntax near "DECLARE": line 1 col 1 (at pos 1). I tried it this way because I received a similar message when adding DO BEGIN to the beginning of the query. After some additional trial and error, I was able to leave out the DECLARE statement and use an in-line declaration (as you provided above) then the query worked (and prompted me for input dates). Thanks for pointing me in the right direction!

Lars Br. Over a year ago

What DB client program do you use? And did you use the :<var-name> naming (with leading colon) as in my example? Without the DO BEGIN you’re not using SQLScript and then there’s only the parameters of prepared statements - which are different from your example.

Satyam Khatri · Accepted Answer · 2023-04-12 14:36:39Z

There is an error in above Answer code, declare endDate and used in where :enDate

Hear below sample query you can use * or column names with "," in SAP HANA DB

DO BEGIN

-- Declare variables
DECLARE startDate DATE;
DECLARE endDate DATE;

-- assign value startDate and endDate 
startDate := '2023-04-01';
endDate := '2023-04-12';

   SELECT 
        T0.*
   FROM "TABLENAME" T0
   WHERE 1 = 1 AND T0."FromDate" = startDate AND T0."ToDate" = endDate;

END;

Or

DO BEGIN

-- Declare variables
DECLARE startDate DATE;
DECLARE endDate DATE;

-- assign value startDate and endDate 
startDate := '2023-04-01';
endDate := '2023-04-12';

   SELECT 
        T0."column1",
        T0."column2"
   FROM "TABLENAME" T0
   WHERE 1 = 1 AND T0."FromDate" = startDate AND T0."ToDate" = endDate;

END;

Collectives™ on Stack Overflow

Syntax for a SAP HANA Query with Input Parameters

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related