Hello,
I need to call from OutSystems several stored procedures on an external database. Each procedure produces a dataset containing a single record. This works quite well with the following construct:
... except for the fact that AI Mentor Studio reports this as problematic due to "Unlimited records in SQL query", even if I set Max. Records = 1:
On another discussion (https://www.outsystems.com/forums/discussion/95209/sql-unlimited-records-in-sql-query-set-identity-insert/) I found a suggestion to add a "SELECT TOP 1 (something)" statement at the bottom of the SQL to be executed, after the EXEC statement.
I find this approach problematic, as it makes the overall SQL return two datasets instead of one, and it may have performance impacts (given that roundtrip to the external DB is far from instantaneous).
Can anyone suggest any other way to suppress the (in my opinion unnecessary) technical debt warning from AIMS? If not, we will just mark these occurrences as false positives.
Thanks
I would just mark it has a false positive. As far as I know the select is the only work around and has you mentioned in this case is not ideal even if the impact is minimal I would rather just set it has false positive.
Anyway I hope someone with more experience can give you (and me) some insight so Im giving it a bump.
Hi Paulo,
There is another option for this technical need. At the end of your SQL query, place this code:
OFFSET 0 ROWS FETCH NEXT @MaxRecords ROWS ONLY
Where the @MaxRecords input parameter will receive the maximum number of records from your query.
I hope I helped you!
Hello, thanks for your suggestion.
Unfortunately, as far as I know (and as confirmed by experiment), the "FETCH NEXT x ROWS ONLY" clause only works at the end of a SELECT statement. It can't be used in the EXEC statement that invokes a stored procedure.
Of course I can limit the number of records returned by the stored procedure (actually, I already do), but AIMS has no way to know that, and keeps reporting the stored procedure call as "unlimited records" technical debt.
I considered converting the stored procedures into table-based functions (so that they can be invoked with SELECT) but there are significant drawbacks that make this approach not viable.
@Paolo Gatti Did you get solution for this? Have you tried with
exec MyStoredProcedure
select @@rowcount
adding a "select top 1 (something) after the EXEC statement is the suggestion I initially found in the forums. Unfortunately, this approach would replace a false positive reported by AIMS with a real piece of technical debt (but undetected by AIMS), which is not the way we want to pursue.
We chose, in the end, to mark the issues reported by AIMS as "false positive".