SELECT fieldnames FROM dynamic SQL query

Question

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 Crystal Report which needs a "static" SELECT with field names it can reference. The Crystal Report is called from a Visual Basic application, and gets it's parameters passed to it from the application. It, in turn, passes the parameters to the SQL Server stored procedure.

Somehow I need to

SELECT fieldname1, fieldname2 
FROM Exec(@MydynamcSQL)

after I build @MydynamcSQL. It is a complicated application accessing specific tables based on year, and specific databases based on the user. I am pretty new to SQL, so maybe there are other methods I could use that I am unaware of?

Answer 1

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

DECLARE @MydynamcSQL varchar(1000);

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

CREATE TABLE #Result
(
  fieldname1 varchar(1000),
  fieldname2 varchar(1000)  
)
INSERT #Result Exec(@MydynamcSQL)
SELECT fieldname1, fieldname1 -- here you have "static SELECT with field names"
FROM #Result 
DROP TABLE #Result