Select column names from one query as the select of another

1

HI

The fun of working with legacy code.

I have a situation where there is some dynamic sql generated as a result of reading column names in from one query and using that as the select of another query. All dynamic sql and I would love to get rid of it.

Can someone provide an example of how to get column names from one query and use those as the select of another

Something along the lines of
Select columnname value from table 1

returns vendorid, vendorname, etc

and then uses that in another query

select vendorid, vendorname, etc from table 2

There are over a 100 columnnames

2

You must use dynamic SQL in SQL Server for something like that. Column names in SELECTs, etc. cannot be variables in SQL Server.

3

thought that was the case -- just hoping there was some other way

4

Are Query1 and Query2 part of the same "package"? or must Query2 work with Query1 without being able to know anything about it?

For example, you could Alias the column names of the result from Query1 so that they were consistent for Query2, or perhaps (guessing what your Package looks like) you could make Query1 a CTE of Query2, or a View that Query2 can use in its query, or output from Query1 to an intermediate table, or somesuch.

If Query2 has to generate a different SQL Statement each time (e.g. with MORE/FEWER columns depending what Query1 returns) then I can't see a way to do that except with dynamic SQL)

Explaining a fuller worked example might enable someone here to make suggestions that will help :slight_smile:

5

yes -- query 2 has to generate different columns dependent on query 1 -- unfortunately dynamic sql is the only to do this