I want to query a function in SQL, and use the returned value in a later step in my scenario.
I have created the function, and created an “Execute a query” module within make, which calls this function. This works, and when I execute this step, I can see the return value.
However, when I try to store this within a variable for later use, I can’t figure out how to do this:
The answer from the function shows correctly (AY-22/23) but nothing here is selectable to populate my variable. I wondered if I needed to use a Text Aggregator to process the output from my Execute a Query step, but that also could not use the output value.
What am I doing wrong?
Incidentally, I don’t really even need the variable - ideally, I’d just use the returned value to populate the parameter in the next SQL “Execute Stored Proc” step:
This is weird, How does your query look like?
Since it is returning a string only without the key, it is at the moment not mappable in other scenarios.
Can you try something like,
select year as CURRENT_ACADEMIC_YEAR FROM YOUR_TABLE
and see if returns the key value required for you to use the mapping in other modules?
Hmmm… Not sure how to do that, because the value I’m returning is from a function, and doesn’t exist within a table. If that’s the cause of the problem, I’m not sure of the way around it…
The query looks like this:
Ah. I take it all back.
Based on what you said, I wondered if it was the lack of a column name that was causing the issue. So, I’ve tweaked the query to be:
SELECT dbo.fnCurrentAcademicYear() AS TheYear
And now that there’s a column alias in the function call, the next module sees the value and is able to select it by name (TheYear).
Thanks for your help!