Select Fieldnames From Dynamic Sql Query

I have a stored procedure that uses several parameters to build a dynamic query, which I execute. The query works fine, however, this procedure will be the data source for a Crysta

Solution 1:

Try creating a temporary table to insert the data temporarily, then select from that table:

DECLARE@MydynamcSQLvarchar(1000);

SET@MydynamcSQL='select fieldname1, fieldname1 from table1';

CREATETABLE #Result
(
  fieldname1 varchar(1000),
  fieldname2 varchar(1000)  
)
INSERT #ResultExec(@MydynamcSQL)
SELECT fieldname1, fieldname1 -- here you have "static SELECT with field names"FROM #ResultDROPTABLE #Result

Solution 2:

Did you try making the who thing dynamic, such as:

Exec( 'SELECT fieldname1, fieldname2 FROM ' + @MydynamcSQL)

It's worth noting although out of scope, ensure you are not vulnarable to sql injection attacks. A parameterized dynamic query potentially leaves you exposed.