sql dynamic
Question

Hi All,

Can you please let me know the dynamic sql formation and expand inline explaination,

I have tried to pass the list to sql but where we can write the expression for in clause.(Example @agelist)

Please provide an example.

Referring this https://success.outsystems.com/Documentation/Best_Practices/Development/Building_Dynamic_SQL_Statements_the_Right_Way


mvp_badge
MVP
Solution

Hi Ujwala,


You just need to have an expanded inline and make sure that they would not break the SQL syntax. Take for example, the screenshot below, when I'm getting user names for a given user list.

This would be more input:


In the end, what would be sent to the database would be the same query but with the WHERE branch with "WHERE {User}.[Id] IN (1,3,4,5)".


Kind Regards,
João

mvp_badge
MVP
Solution

Hello Ujwala.


It is quite simple if you know the rules.


1. It must be an Advanced SQL. Can't use this in an Aggregate.

2. The parameters must have Expand Inline = yes

3. You must follow the SQL rules. Example: for this List of If, I must write "(1,2,3,4,5)" so that my IN clause has parenthesis.

If your parameter doesn't have parenthesis, you have to write them in the expression (the link you checked shows that case "{Users}.[Id] IN (@idlist) )"


It can even go more extreme, like this.

Here you have to pass something like " AND {User}.[Id]>100"


There are no limits on what you can do with expand. Just remember it is not great for performance because it doesn't allow optimization.

mvp_badge
MVP
Solution

Hi Ujwala,


You just need to have an expanded inline and make sure that they would not break the SQL syntax. Take for example, the screenshot below, when I'm getting user names for a given user list.

This would be more input:


In the end, what would be sent to the database would be the same query but with the WHERE branch with "WHERE {User}.[Id] IN (1,3,4,5)".


Kind Regards,
João

mvp_badge
MVP
Solution

Hello Ujwala.


It is quite simple if you know the rules.


1. It must be an Advanced SQL. Can't use this in an Aggregate.

2. The parameters must have Expand Inline = yes

3. You must follow the SQL rules. Example: for this List of If, I must write "(1,2,3,4,5)" so that my IN clause has parenthesis.

If your parameter doesn't have parenthesis, you have to write them in the expression (the link you checked shows that case "{Users}.[Id] IN (@idlist) )"


It can even go more extreme, like this.

Here you have to pass something like " AND {User}.[Id]>100"


There are no limits on what you can do with expand. Just remember it is not great for performance because it doesn't allow optimization.

Be aware that the examples above are not very secure. 

If the paramter van be given through a webservice for example, someone could see all the data they want:

@MyConditons = " or {User}.[Is_Active] = 0"

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.