Friendly DataSet Table Names

I have a stored procedure that returns a set of tables based on data in a table for a company. These tables are being used to create drop down lists for criteria selection for the client. We have a Javascript based control that will make use of these based on the control name. So... ideally, I would like the stored procedure to return tables named "EmployeeID", "ResourceName", so that I can accurately name the controls when they are being created. The data that is returned is not static, so, for example, client 1 may see the EmployeeID drop down, while client 2 may not.

I searched and didnt see anyone that was able to accomplish this in this way, but wanted to post something here before I moved forward in a different direction.

I am thinking that I will likely return an extra table with the field names and their corresponding tables (i.e. "EmployeeID"/"Table0", "ResourceName"/"Table1", etc...)

Thanks,

Josh

Have you tried giving alias to the returned tables (result sets) as well as columns? For example:

create proc sp_RtnAlias
as
select EmployeeID EID from Employees as EmployeeID
go

Then you can access the result set using EmployeeID.EID

Hope I haven't misundstood you.

|||

Here's what I do... and I haven't decided if it is cheesey or not...

declare

@.tbltable(Ordinalintidentity(1,1),tableName varchar(50))

/* Insert a row into the temp table for each table returned by the query*/

INSERTINTO @.tblVALUES('Prospects')

INSERTINTO @.tblVALUES('AccountExecs')

INSERTINTO @.tblVALUES('Regions')

INSERTINTO @.tblVALUES('Products')

INSERTINTO @.tblVALUES('ProspectDescriptors')

INSERTINTO @.tblVALUES('Steps')

INSERTINTO @.tblVALUES('ProspectTypes')

INSERTINTO @.tblVALUES('VendorTypes')

INSERTINTO @.tblVALUES('Clients')

-- Index table

SELECT Ordinal, TableName

FROM @.Tbl

ORDERBY Ordinal

The rest of the sproc is a series of selects in the same order as they appear in the index table. I have method in the program that loops through the table objects and assigns each one the appropriate name from the index table. Note that the index table does not contain a reference to itself and can be disposed of when the other table names have been assigned...