Dynamic SQL Syntax Error SQLScript SAP HANA

Question

I have dynamic SQL in a Stored Procedure.

The procedure takes three parameters:

DimPartialName
ColumnName
UploadID

Here is the procedure:

PROCEDURE "Schema"."DeletefromDIM" (In DimPartialName NVARCHAR(50), In 
IDColumnName NVARCHAR(50), IN UploadID NVARCHAR(36) ) 
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER 
--DEFAULT SCHEMA <default_schema_name>
AS
BEGIN
EXECUTE IMMEDIATE
'select distinct "'|| IDColumnName ||'" from 
"Schema"."ZT.'|| DimPartialName ||'" dim
LEFT JOIN "Schema"."SourceTable" raw on 
dim."AUDIT.CREATED_ON" = raw."Audit.CREATED_ON"
where "UPLOAD_UUID" = ' || UploadID ||' ';
End;

And the Error:

SAP DBTech JDBC: [257]: sql syntax error: "Schema"."DeletefromDIM": line 15 col 1 (at pos 520): [257] (range 3) sql syntax error exception: sql syntax error: incorrect syntax near "-": line 3 col 41 (at pos 214)

when I run this SQL in the console and substitute in my inputs, then it works fine:

select distinct "BRANDID" from "Schema"."ZT.BRAND" dim
LEFT JOIN "Schema"."SourceTable" raw on dim."AUDIT.CREATED_ON" = raw."Audit.CREATED_ON"
where "UPLOAD_UUID" = 'f889e016-1364-4aac-9536-037d932c55b5';

The error doesn't really make sense because not only is there no "-" at that position, there is no dash in the entire statement.

Change it to Varchar(max), its error while attempting to evaluate the string unique identifier data , always good idea to Print @sql to check if script is correct. In short, your script is not enclosing variable value in single quotes. — Ven
– Ven, Commented Nov 1, 2018 at 16:40

Lars Br. · Accepted Answer · 2018-11-02 03:23:37Z

2

You should check the generated SQL string by selecting it for output, like so:

BEGIN

        select                'select distinct "'|| IDColumnName ||'" from 
                        "Schema"."ZT.'|| DimPartialName ||'" dim
                        LEFT JOIN "Schema"."SourceTable" raw on 
                        dim."AUDIT.CREATED_ON" = raw."Audit.CREATED_ON"
                        where "UPLOAD_UUID" = ' || UploadID ||' ' as SQLTEXT from dummy;

End;

If you do this, you'll discover that the SQL statement generated looks like this (for input of 'X', 'Y', 'Z'):

select distinct "Y" from 
                    "Schema"."ZT.X" dim
                    LEFT JOIN "Schema"."SourceTable" raw on 
                    dim."AUDIT.CREATED_ON" = raw."Audit.CREATED_ON"
                    where "UPLOAD_UUID" = Z

Notice how the WHERE condition "UPLOAD_UUID" = Z lacks single quotation marks around the Z.

Changing the statement to

BEGIN

    execute immediate 
                  'select distinct "'|| :IDColumnName ||'" ' 
               || 'from '
               || '"Schema"."ZT.'|| :DimPartialName ||'" dim '
               || 'LEFT JOIN "Schema"."SourceTable" raw on '
               || 'dim."AUDIT.CREATED_ON" = raw."Audit.CREATED_ON" '
               || 'where "UPLOAD_UUID" = ''' || :UploadID ||''' ';

END;

should fix the issue.

answered Nov 2, 2018 at 3:23

Lars Br.

10.5k2 gold badges19 silver badges31 bronze badges

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

1 Comment

ThomasRones Over a year ago

Thanks, I didn't realize I could print dynamic SQL. Part of the reason I find SQL so hard to debug is because I have no console that I can print to, but I guess there sort of is.

Collectives™ on Stack Overflow

Dynamic SQL Syntax Error SQLScript SAP HANA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related