Can someone explain the use of Expand inline parameter in SQL tool with example?
Parameters are normally converted to actual SQL parameters. When "expand inline" is set however, whatever is in that parameter will not be a SQL parameter, but a literal piece of SQL that's merged into the SQL query, before it's converted and sent to the database.
So say I have the following SQL:
SELECT {MyEntity}.* FROM {MyEntity} WHERE {MyEntity}.[UserId] = @UserId @AdditionalWhereClauses
@UserId being a normal parameter and @AdditionalWhereClauses being an expand inline parameter, you could put the following SQL in the AdditionalWhereClauses parameter:
AND {MyEntity}.[UserName] LIKE '%smith%'
and it would filter on users with "smith" in their username.
Typical uses for expand inline parameters are variable lists of IN values or variable WHERE clauses.
Thanks Kilian Hekhuis.
You're welcome!
Hi! Checking this post, I find it crucial to say something about security.
As you can find in the documentation about Injection and Cross-Site script, "When you set the Expand Inline property to Yes you deactivate the default escape content from the platform, and you need to take care."
This opens a door for a SQL injection and creates a flaw in your app, so being aware of it it´s a must. I hope this helps someone :)
Yes, you should be aware, and in general should avoid taking user input (directly or indirectly) and using it directly in the SQL. However, in cases where there is no user input, but just literals in the code, there's no injection possible.