Inserting results of first dynamic sql statement into second one

[goofaholix]

I have a dynamic sql statement that when executed returns a list of columns, I want to insert the results of this into a second dynamic sql statement as a list of columns that the second statement will return.

The first statement returns something like;
column1,
column2,
column3,

The second statement is something like;

SET @statement2 = 'Select
UID,Type,' + @statement1 + '
getdate()
FROM table
Where ID in (' @parameter ')'

When I do the above it prints the first statement inside the second whereas I want to execute the first statement inside the second.

The reason i need to do this is that the first statement determines which columns are appropriate to be included thin the select statement of the second statement based on the parameter.

Any idea how I should do this?

thanks.

Bruce

 

[RyanEK]

Hi,

Dynamic sqls should be avoided and used with caution. However to answer your question, you could do the following:

-- Create delimited string using your 1st statement
DECLARE @columns varchar(100)
SELECT @columns = COALESCE(@columns + ', ', '') +  [Field] FROM [Table]

-- Create 2nd statement
SET @statement2 = 'Select
UID,Type,' + @columns + '
getdate()
FROM table
Where ID in (' @parameter ')'

Ryan

 

[goofaholix]

Coalesce appears to return the first columnname that isn't null as I get the same column name over and over in a list.

This isn't what I'm after.

To clarify, I have a table in object oriented format, as below, there are hundreds of these columns and some are only used for some parameters, some for all;
ID Column Value
1 column1 123
1 column2 null
1 column3 abc

I have used pivot to create with a normalised table like this;
ID Column1 Column2 Column3
1 123 null abc

Now I want to run a select on the original table to determine for a given parameter what columns I should include in the select statement of a select on the second table. As there are hundreds of columns and most won't be used for a given parameter I don't want them in my resultset.

thanks

Bruce