Dynamic SQL with input parameters

[Former Member]

Hello,

I would like to create dynamic SQL using a WHERE statement.

The user would enter P_Location as an input parameter and then I would like to do something like:

WHERE "Location" = :P_Location

Thank you,

Fred

[Former Member]

Actually just figured out how to do it:

WHERE "Location" >= :P_Location

But the only issue is that is does not actually substitute.

For example I get results like, Location="R300"

When I entered P_Location=R110

[lucas_oliveira]

hi Fred,

Send your code and sample data so we can try helping you out.

BRs,

Lucas de Oliveira

[former_member251619]

Yes, the result is totally right. You're telling to return all data, that is larger/equal than/to the string R110 (R300 obviously is).

You might try something like:

DECLARE temp NVARCHAR(255);

IF : P_Location = ''

THEN

     :temp = '%';

ELSE

     :temp = : P_location

ENDIF.

...WHERE  Location LIKE :temp;

Please tell me, if this works for you.

Kind regards,

Daniel

[lbreddemann]

Instead of fiddling with the SQL statement and creating dynamic SQL manually, you might want to check the SAP HANA documentation and use the APPLY_FILTER (APPLY_FILTER - SAP HANA SQLScript Reference - SAP Library) function.

This removes your boilerplate code and makes it pretty explicit, what you are doing there.