Use @FromSubquery to run against a SQL Function and pass a parameter to the function at runtime

[Sancha465]

Summary

I have a SQL database function which takes a date as a parameter. I'm using @FromSubQuery on my model to run the function and return the results. That part is working perfectly. However, the parameter is currently hardcoded within that annotation. I need the ability to pass a parameter at runtime based on user input.

Snippet of the SQL Function:

CREATE FUNCTION dbo.MY_REPORT (@Parameter varchar(16))
  RETURNS TABLE
  AS
  RETURN
  (
   SELECT .....
   )
  );

Snippet showing the annotations used on the model to run against the SQL Function:

@Entity
@FromSubquery(sql = "SELECT * FROM dbo.MY_REPORT(CAST( GETDATE() AS Date))")
@Table(name = "MY_REPORT")
@AttributeOverride(name="id", column=@Column(name="MY_REPORT_ID"))
@Include(rootLevel = true, name="myReport")
public class MyReport {}

Expected Behavior

We need a way to specify the parameter at runtime so that we can run the same function and get results for different dates based on user input.

Current Behavior

The @FromSubquery annotation takes the sql query string (e.g., "SELECT * FROM dbo.MY_REPORT(CAST( GETDATE() AS Date))")
but that means the parameter is hardcoded to today's date.

I need a way to specify the parameter portion as a variable that can be modified at runtime.
CAST( GETDATE() AS Date)

Your Environment

• Elide version used: 5.0.4
• Environment name and version: Java jdk11.0.15
• Database: SQL Server

[aklish]

Elide supports both column and table arguments for analytic queries:

https://elide.io/pages/guide/v6/04-analytics.html#arguments

You can then template your SQL query using these arguments. Note that it is only possible for a client to furnish argument values to a query using GraphQL. Json-API doesn't have any language mechanics for parameterizing fields or collections.