Hi Roxanne,
Have you tried writing VB code that uses ADO to execute your built queries?
You would need to return the SQL queries in a resultset. So, you might add the following code at the stored proc
declare @sql_stmts table (
cols varchar(8000),
from varchar(8000),
where varchar(8000)
)
-- repeat insert below for each query
insert into @sql_stmts values(
<comma delimited list of columns in SQL query would go here>,
<SQL query FROM clause (i.e. inner joins also) would go here>,
<SQL query WHERE would go here>
select * from @sql_stmts
Then, the VB code might look something like this:
Dim cn as ADODB.Connection, rs as ADODB.Recordset
cn.Open "DB conn str goes here"
Set rs = cn.Execute("spQuery 'SomeQuery', '5' ")
Dim strSqls as String
'building the SQL statements
While (Not rs.EOF)
strSqls = strSqls & "SELECT " & rs(0) & " FROM " & rs(1) & " WHERE " & rs(2) & ";"
rs.MoveNext
Wend
'now, execute all the SQL queries in one execute call
cn.Execute(strSqls)
Hope this helps,
JP