SQL Server Performance

output result set of a stored procedure to a table

Discussion in 'SQL Server 2008 General Developer Questions' started by WingSzeto, Mar 25, 2009.

  1. WingSzeto Member

    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.
  2. FrankKalis Moderator

    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?
  3. Madhivanan Moderator

  4. WingSzeto Member

    Thanks. Option 2 is what I need.
  5. geebee2 New Member

    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.

Share This Page