I have an application that utilizes a highly complex series of nested queries. This application is completely dynamic from the ground up, so the queries are very modular with a lot of parameter passing. The original architecture was VB6 and Access 2000, and I have been migrating to VB6 with SQL Server 2000.
Here is my problem / question.
With Access 2000 I could nest Parameter queries. For example, I have Q1 called by Q2 called by Q3 called by Q4 all through the "FROM' or the 'JOIN' clause. If Q2 has a parameter, Access is smart enough to pull that paramater all the way up to Q4 when VB calls it. So I pass Q4 the parameter and Q2 acts on it.
Now in SQL Server 2000, VIEWS do not support parameters, so I needed to user Stored Procedures. Stored Procedures, however, do not seem to Nest like I need them to. SPs seem to only nest in the "From" clause so your can basicaly limit the results.... but I need it to nest on the "Join".
Here is an example:
SELECT Q1.*, Q2.X
FROM Q1 LEFT JOIN Q2 ON Q1.ID = Q2.ID;
Q2 has a parameter it accpets, and Q1 has none. While this is extremely easy in Access 2000, I can't seem to find a way to do it in SQL!
Can anyone think of a way I can do this type of Join expression while passing Q2 the proper parameters. Or even thinking outside the box, is there another way to go.
I have also considered if I could somehow perform the Q2 and store in a VB Dynaset and then use the join expression on Q1 or something along the line