Hi John,
The code I presented before had names changed to make it more generic so it was not exactly what I'm doing. I did get the search through EXEC_SP working but discovered I needed to do SQL JOINs to included reference attributes in the search criteria and it all got to hard.
That said I've now got a better understand of the strengths of SQL Stored Procedures (thanks to BLOMASKY's excellent video
https://recordings.join.me/TNfswyFvc0aA76GVKJmwgg). Looking at the operations for an equivalent SQL query has made me realise I could get the same result using a normal AwareIM Query (which is so much easier).
That said I do have an additional future requirement to do geocoding queries (eg get all the geo points that fall within a randomly drawn geo region). I suspect this query will need to be done in SQL as many SQL's now have native geocoding functionality. I'd prefer to do it all in Aware but I'm suspecting geocoding queries are a valid reason to do that in SQL?? I'm thinking I might still do all the other attribute criteria FINDing in Aware and pass the results to the stored procedure (eg "FIND Person with Age between 10 and 30 and gender Female", then pass that result to the SQL to filter by geo location).
Regarding your question on why I'm bypassing User Defined Queries, my reason were :
- Change the User Interface
- Restrict what objects and attributes different users had access to
- Store different queries in different categories and possible owned by different users