• Replies 3 replies
  • Subscribers 961 subscribers
  • Views 97 views
  • Users 0 members are here
Related

Dynamic SQL in SSRS: to SProc or not to SProc

Heath Wilder
$organization

Hi Folks,

So I've created a Dynamic SQL query for WordFly Pages (Survey) results with column headings being the Questions and Answers that the kids over here come up with.  They change for each different survey obviously.

Am I right in thinking that I can't run that as a Stored Proc to SSRS because the fields are ever changing?  And if so will I have to run the Dynamic SQL as a Dataset Query in SSRS directly?

Many thanks,

Heath

An eg: on one possible test output with 3 question fields

customer_no esal1_desc esal2_desc business_title 1 Are you Coming 2 How many additional guests will attend? 3 Do you have any dietary requirements
142568 Heath Wilder Sydney Dance Company CRM & Ticketing Manager yes 1 Nope
155500 Ms Anne Brito Sydney Dance Company Marketing Coordinator yes 1 i dont eat salad
Parents
  • $organization
    Heath Wilder said:
    Am I right in thinking that I can't run that as a Stored Proc to SSRS because the fields are ever changing? 

    Technically, yes, you can run dynamic SQL within a sproc, and send the output to an SSRS report, but it's usually a fiddly process, e.g. either decide you don't care how many output columns you have, and send the data to a matrix, or decide a maximum number of output columns that you will never exceed, create a matching output table in the report, and hide the ones you aren't using for this particular run, or the like. Not very elegant, but it can be done.

Reply
  • $organization
    Heath Wilder said:
    Am I right in thinking that I can't run that as a Stored Proc to SSRS because the fields are ever changing? 

    Technically, yes, you can run dynamic SQL within a sproc, and send the output to an SSRS report, but it's usually a fiddly process, e.g. either decide you don't care how many output columns you have, and send the data to a matrix, or decide a maximum number of output columns that you will never exceed, create a matching output table in the report, and hide the ones you aren't using for this particular run, or the like. Not very elegant, but it can be done.

Children
No Data