Multiple Table names in result data set.

I have the below query. Basically it takes from a table the names of other tables. This seems to work but the result data set has tables named as Table, Table1, etc. How can I change the names of the tables in the result set?

Thank you.

Kevin

BEGIN

SET NOCOUNT ON;

DECLARE @.TableName VARCHAR(50)

DECLARE ReasonCategory_Cursor CURSOR FOR

SELECT CATEGORY

FROM CaseNoteReasonCategories

SELECT CATEGORY

FROM CaseNoteReasonCategories

OPEN ReasonCategory_Cursor;

FETCH NEXT FROM ReasonCategory_Cursor INTO @.TableName;

WHILE @.@.FETCH_STATUS = 0

BEGIN

EXEC ('SELECT * FROM ' + @.TableName + ' AS ' + @.TableName);

FETCH NEXT FROM ReasonCategory_Cursor INTO @.TableName;

END;

CLOSE ReasonCategory_Cursor;

DEALLOCATE ReasonCategory_Cursor;

END

Your query returns the names of the tables as stored in the CATEGORY column of the CaseNoteReasonCategories table.

If you want different table names, you might try changing the values in the Category column.

|||

The problem is that the name(s) of the tables do not correspond to the names in the category column. It seems that the names of the tables is fixed at Table, Table1, Table2, etc. This seems to be the case no matter what the names of the tables are in the CATEGORY column.

I have started to look some more into this and this may be an aritfact of ADO.NET. But I am not sure how to override what it is doing so I get the names of the tables corresponding to the names in the CATEGORY column.

|||

I don't believe this is an issue with ADO.NET. Your query is pulling the actual values out of the Category column of the CaseNoteReasonCategories. I suspect that there is some 'test' data in the table and that is what you are seeing.

Your query does not have a WHERE clause, so it will be gathering all rows from CaseNoteReasonCategories, and I suspect Table1, Table2, etc., are the first rows in the table.

Also, in the line below, the + ' AS ' + @.TableName has no value.

EXEC ('SELECT * FROM ' + @.TableName + ' AS ' + @.TableName);

|||

Arnie Rowland wrote:

I don't believe this is an issue with ADO.NET. Your query is pulling the actual values out of the Category column of the CaseNoteReasonCategories. I suspect that there is some 'test' data in the table and that is what you are seeing.

Your query does not have a WHERE clause, so it will be gathering all rows from CaseNoteReasonCategories, and I suspect Table1, Table2, etc., are the first rows in the table.

Also, in the line below, the + ' AS ' + @.TableName has no value.

EXEC ('SELECT * FROM ' + @.TableName + ' AS ' + @.TableName);

The values in the Category column are the names of the tables. These values are NOT Table1, Table2, etc.

I don't have a WHERE clause because I want all of the rows from CaseNoteReasonCategories. Table1, Table2 are not the first rows in this table.

Adding the 'AS' to the end of the query seems to have not effect. I have also tried

EXEC ('SELECT * FROM ' + @.TableName + ' ' + @.TableName);

|||

It is quite a mystery where those table names are being 'automagically' created...

My point was that assigning an alias for the table (with or without using 'AS') has no value since you are not using the alias in your code. It is wasted effort -albeit of little consequence.