The Old Joel on Software Forum: Part 4 (of 5) - Naming Result Sets return by Stored Proc

Naming Result Sets return by Stored Proc

I'm working with stored procedures written by a DBA (on SQL server). One of the stored procedures returns multiple result sets. Each result set represents data from slightly different tables, the column names are the same, but the data needs to load into different spots.

I'd like some way to access these result sets by name rather than just depending on the ordering of the results that come back. Is there a simple way to "name" the result sets? I suggested adding an extra column to each result set - but that was frowned upon. I'm worried that the stored proc might get altered in the future and the ordering of the result sets will change (thus wreaking havoc throughout my data access code).

Hardly anyone access columns by numeric ids anymore. It seems like accessing result sets by name would be a good idea too. Am I way off base?

NathanJ
Wednesday, April 14, 2004

There isn't a way to do this. You can read the names of the columns, which might be sufficient to determine which result set you are dealing with.

If you use the result set number approach, make sure that logic does not keep a result set from being produced. That is, make sure the result set is always produced even if it is empty. (Note that you can produce dummy result sets.)

Also, it appears that SQL server procs always produce a result set (even if there are no queries). Check the number of columns to be sure it's a from-a-query result set.

njkayaker
Wednesday, April 14, 2004

Also, if your query does not have column names, add the column names. (E.g. "select 1 'a_name'").

If you produce dummy result sets, use "convert" to produce the correct column data type. You can convert NULL (of "no type") to NULL of a particular type.

njkayaker
Wednesday, April 14, 2004

You could make the first or last recordset a listing of the other recordsets. Here's an example using the last recordset:

[SQL CODE]

/* If using SQL Server, do the following for statements
that might send a "Rows Affected" message, otherwise
ADO will create blank recordsets... */
SET NOCOUNT ON

UPDATE Authors SET [Name] = @Name WHERE [ID] = @ID

SET NOCOUNT OFF

-- RS # 1 ('Authors')
SELECT [ID], [Name] FROM [Authors]

-- RS # 2 ('Books')
SELECT [ID], [Name] FROM [Books]

-- RS # 3 ('Publishers')
SELECT [ID], [Name] FROM [Publishers]

-- RS # 4 (Listing of other recordsets)
SELECT 'RS1'='Authors', 'RS2'='Books', 'RS3'='Publishers'

[CLIENT PSEUDO CODE]

' GetAllRecordsets returns an array of recordsets
' that are contained in RecordsetFromServer.

RsArray = GetAllRecordsets(RecordsetFromServer)

RsLast = RsArray(RsArray.UBound)

j = RsLast.Fields.Count - 1

For i = 0 to j
sNameOfRs = RsLast.Fields(i).Value
Select Case sNameOfRs.toUpperCase()
Case "AUTHORS"
rsAuthors = RsArray(i)
Case "BOOKS"
rsBooks = RsArray(i)
Case "PUBLISHERS"
rsPubs = RsArray(i)
End Select
Next

Wayne
Wednesday, April 14, 2004