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?
1 Like
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:
SELECT dbo.fnCurrentAcademicYear()
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!
2 Likes
