Generating one recordset from another (output of stored proc)

[BruceG]

Hello all,
I have a "SELECT" stored procedure which returns a recordset consisting of about 20 fields. After calling the sp, I use the recordset to populate listview. All of this works fine.
The pertinent code is basically:

Dim strSQL As String
strSQL = "sp_MyStoredProc ... "
Set rtemp = OpenRecordset(strSQL, adOpenStatic, adLockReadOnly)
While Not rtemp.EOF
    ' populate listview item
    rtemp.MoveNext
Wend

What I'd like to do now is create a "summary" version of my process, which would conceptually consist of using the "rTemp" recordset as if it was a table, where I could either use it in the VB6 program, or create another sp that calls the original sp and processes the origninal sp's resultset.
Conceptually:
"SELECT col1, col2, sum(col3) as the_total FROM rtemp GROUP BY col1, col2"

What are my options for this, if either scenario is possible?

(I know I could write an alternative "summary" version of the original sp, however, all the "hard work" in generating the detailed result recordset is done in the original sp, and I would prefer not to have to duplicate my efforts as well as maintain the code in two places should the general requirements of the detail query change.)

[Schmidt]

That kind of magic is possible with the COM-wrapper for SQLite (vbRichClient5 or RC6),
since it supports not only InMemory-DBs, but also appropriate loading-functions for ADO-Rs.

Here is fully working example-code, which needs:
- a reference to either vbRichClient5 or RC6
- a reference to ADO (2.5 or 2.6 is enough)
- a component-reference to the MS Hierarchical FlexGrid
- a roughly placed instance of the above Grid on your Form under its default-name

Option Explicit

Private MemDB As cMemDB

Private Sub Form_Load()
  Set MemDB = New_c.MemDB
End Sub
 
Private Sub Form_Click()
  MemDB.Cnn.CreateTableFromADORs MemDB.Cnn, "T1", NewDemoRs
  
  Dim AdoRs As ADODB.Recordset
  Set AdoRs = MemDB.GetRs("Select Col1,Col2,Sum(Col3) From T1 Group By Col1,Col2").GetADORsFromContent
  Set MSHFlexGrid1.DataSource = AdoRs
End Sub

The part I've marked blue above, is a function which returns a DemoRs (which you could exchange to your real one).
Here is the code for it (to be placed in the same Form)

Private Function NewDemoRs() As ADODB.Recordset
  Set NewDemoRs = New ADODB.Recordset
      NewDemoRs.Fields.Append "Col1", vbLong
      NewDemoRs.Fields.Append "Col2", vbString
      NewDemoRs.Fields.Append "Col3", vbLong
  NewDemoRs.Open
  
  'now we insert a few records, forming 4 "Groups"
  NewDemoRs.AddNew Array(0, 1, 2), Array(1, "A", 1)
  NewDemoRs.AddNew Array(0, 1, 2), Array(1, "A", 1)
  
  NewDemoRs.AddNew Array(0, 1, 2), Array(1, "B", 2)
  NewDemoRs.AddNew Array(0, 1, 2), Array(1, "B", 2)
  
  NewDemoRs.AddNew Array(0, 1, 2), Array(2, "C", 3)
  
  NewDemoRs.AddNew Array(0, 1, 2), Array(3, "D", 4)
End Function

Such a MemDB-approach allows you to insert more than a single Table of course -
so that you can perform even complex clientside Joins across several "parked Rs-Tables" there.

HTH

Olaf

[BruceG]

Thanks - interesting - will take this under advisement ...

[dilettante]

Back in the 1970s we got a thing in SQL called a subquery. Many dialects of SQL that support stored procedures allow calls to them to be used as subqueries.

[BruceG]

Thanks dilettante - I am well aware of subqueries - using SQL Server here (an older version), I do not believe SQL Server supports what you are suggesting here (if wrong, I am all ears ...)

[dilettante]

I was pretty sure you could as long as the stored procedure is written as a table-valued function.

Another option might be to query it into a temporary table.

Or you could write a custom aggregator class, e.g.:

Option Explicit

Private Groups As ADODB.Recordset
Private Names As Variant

Public Sub Aggregate(ByVal RS As ADODB.Recordset)
    Dim newf1 As ADODB.Field 'Optimize access.
    Dim newf2 As ADODB.Field
    Dim newf3 As ADODB.Field
    Dim f1 As ADODB.Field 'Optimize access.
    Dim f2 As ADODB.Field
    Dim fS3 As ADODB.Field

    With RS
        With .Fields
            Set newf1 = .Item("col1")
            Set newf2 = .Item("col2")
            Set newf3 = .Item("col3")
        End With
        If Not Groups Is Nothing Then Groups.Close
        Set Groups = New ADODB.Recordset
        With Groups
            .CursorLocation = adUseClient
            With .Fields
                .Append "col1", adBoolean
                .Append "col2", adInteger
                .Append "SUM(col3)", adInteger
                Groups.Open 'Makes the appended fields valid.
                Set f1 = .Item("col1")
                Set f2 = .Item("col2")
                Set fS3 = .Item("SUM(col3)")
            End With
            f1.Properties.Item("Optimize").Value = True 'Creates a temporary index.
            f2.Properties.Item("Optimize").Value = True
            'Aggregate:
            Do Until RS.EOF
                'Group by col1, col2 (using a hack because newf1.Type = adSmallInt, not adBoolean):
                .Filter = "col1=#" _
                        & CStr(CBool(newf1.Value)) _
                        & "# AND col2=" _
                        & CStr(newf2.Value)
                If .EOF Then
                    'Add new group:
                    .AddNew Names, Array(newf1.Value, newf2.Value, newf3.Value)
                Else
                    'Sum new f3/col3 value:
                    fS3.Value = fS3.Value + newf3.Value
                End If
                RS.MoveNext
            Loop
            .Filter = adFilterNone
        End With
        .MoveFirst
    End With
End Sub

Public Property Get Recordset() As ADODB.Recordset
    Set Recordset = Groups
End Property

Private Sub Class_Initialize()
    Names = Array("col1", "col2", "SUM(col3)")
End Sub

Private Sub Class_Terminate()
    Groups.Close
End Sub

See the comments. A bit of fudging is going on here because of a bug/limitation in the MS Jet SQL Expression Service related to Boolean expressions.

[techgnome]

Its been a while, but yes, you can exec an sproc into a temp table (does not need to be a TVF)... I forget the exact syntax, but it is possible. The other options is also to simply do it client-side, as olaf & Dil have shown. All of which are perfectly viable options. Just depends on which method you're more comfortable with.

-tg