I have a stored procedure which generates a result set dynamically. More specificially, the number of columns in the result set varies from time to time. the sp may generate 5 columns at one time and 20 another time. Is there a way that when the procedure is run, it output the dynamic result set to a table or #temp table not knowing the # of columns? I read about this subject and so far all required me to create the temp table first, which I can't because of the dynamic result set. Any pointer on this is very much appreciated. W
True. Why does it need to be dynamic? Why not always give a fixed column output back and let the client consume whatever columns he wants?
If you cant change the current design, refer method 2 http://sqlblogcasts.com/blogs/madhi...rom-exec-procedure-name-is-this-possible.aspx
Can you not use some dynamic SQL?i.e. Have some dynamic SQL that creates an appropriate table, then executes the stored procedure, storing the data.This assumes only that the number of columns can be predicted somehow.