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

Sancha465 · 2022-11-09T14:24:34Z

Sancha465
Nov 9, 2022

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 · 2022-11-13T22:53:40Z

aklish
Nov 13, 2022
Maintainer

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.

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

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

Uh oh!

{{title}}

Uh oh!

Replies: 1 comment

Uh oh!

{{title}}

Uh oh!

Select a reply

Uh oh!

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

Uh oh!

Sancha465 Nov 9, 2022

Summary

Expected Behavior

Current Behavior

Your Environment

Replies: 1 comment

Uh oh!

aklish Nov 13, 2022 Maintainer

Sancha465
Nov 9, 2022

aklish
Nov 13, 2022
Maintainer