none
Getting column names from dynamic SQL Query RRS feed

  • Question

  • Hi,

    I am working on a report that has a dynamic SQL string as input. This dynamic SQL will sometimes deliver a result set with 5 columns and other times with 7 columns. I am trying to use the tablix control in a report to make a listing of the result set provided by the dynamic SQL. In order to do so, I have customized the query, which is obtained via an intermediate stored procedure, i.e. the report calls the stored procedure and the stored procedure calls the dynamic SQL and transforms the data for the report.

    I need to get the aliasses from the result set, so I can deliver the data correctly to the report. Is there a way to get the column names / aliases from the result set?

    Summarized, I run a dynamic SQL query from a stored procedure, which results in a variable width result set. How can I read the column names / aliases for each of the columns?

    I do not want to use the Visibility property of unused columns in the report, since I do not know the maximum numbers of columns in the result set.

    Friday, March 11, 2011 1:08 PM

Answers

All replies

  • Hi Rodney Heinkens !

    I think its more related to SSRS rather than T-SQL. May be posting your question on below forum will help;

    http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/threads

    Thanks, Hasham

     

    Friday, March 11, 2011 1:36 PM
    Answerer
  • One solution is to make a metadata version of the stored procedure (sprocAlphaMD) which would return the required information into a second dataset.

    Related link: http://sqlusa.com/bestpractices2005/dynamicpivot/

     


    Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM
    • Marked as answer by KJian_ Thursday, March 17, 2011 7:16 AM
    Thursday, March 17, 2011 7:07 AM
    Moderator
  • Hi Hasham,

    My question is purely SQL, not related to SSRS. Just because I am using the stored procedure as input for a report does not make this question about SSRS.

    Cheers,

    Rodney

    Thursday, March 24, 2011 8:24 AM
  • In your stored procedure

    for example @sql contains your dynamic query, you have below statement to get your result set.

    exec(@sql)

    append 1=1 condition in your query and execute @sql once again so that you will get 2 result sets, first one with data, second one only column names from there you can do play with it.

    set @sql = @sql + ' and 1=1' -- if @sql ends with  where clause

    set @sql = @sql + ' where 1=1'  -- if @sql does not ends with  where clause


    If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer. DVR
    Thursday, March 24, 2011 9:00 AM