Hi, I had to do something like that in the past.
To reuse the SQLDataSet of Kedro, I split the query in two steps.
In the first, I was using a TextDataset with parameters that were replaced in one node (for example with a .format).
In the second, the node had the filled SQL query and the connection string as inputs, was instantiating a SQLDataSet and returning the result of the load. This node is in fact generic.
I liked this implementation because it was very flexible and allowed me to put all my SQL queries in a dedicated folder and to use a linter for the queries.