I’m using SQL Server. Is there a correct way to include Declare statements at the start of a SqlSelect so that it will return a proper RecordSet with Xojo?
I don’t want to use the “?” parameters as what I’m doing is more complicated and has nested queries.
Essentially it needs to act as a SqlExecute for the first part but then do the SqlSelect to return the RecordSet. I can’t separate these commands however as the variables go out of scope if I do.
Not sure how you think a declare would help here ?
You can use ? with nested queries on sql server
I do that all the time
IF however you need a variable subquery (nested query) thats a different thing entirely
Post the query?
The queries I’m using are dynamically created and nested.
They support things like being wrapped in a count:
SELECT Count(*) FROM ({subquery})
And they support being paginated:
SELECT * FROM ({subquery}) WHERE {filter} ORDER BY {sort} OFFSET {page}*{pageSize} ROWS FETCH NEXT {pageSize} ROWS ONLY
And then they dynamically load data into my WebListView.
So I have to hoist the declares from the query to the top of the query and subqueries can have any number of variables so keeping track of the “?” with all the nesting queries doesn’t make as much sense as variable names.
But if you want to see an example of the type of stuff I’m trying to do:
In order to dynamically render the columns of the pivot table you have to use EXECUTE sp_executesql @sql; with the Declared @sql variable.
OK this isnt a “declare” in the Xojo sense of the word 
Just an execute of a stored procedures (sp_executesql) that happens to execute the sql you pass
Since this returns a result set as rows you _should _ try using SelectSQL but to be honest I dont think you’ll be able to pass your query to it (not sure that the param, @sql, can be a bound variable for the input)