setting 19 params without going to the well 19 times RRS feed

  • Question

  • hi we run 2016 enterprise.  i have 19 parameters that can be set by selecting the top 1 row from one table using the value entered in the 20th parameter as a key to that table.

    rather than 19 visits to the db is there a way to cache the results of one trip in a way that can be used to set the 19 params from memory?

    Friday, August 7, 2020 9:37 PM

All replies

  • Hi

    you can pass all the values separated by comma as a string value again split in sql server and have to convert as per your required data type

    Thanks and regards

    Saturday, August 8, 2020 5:46 PM
  • Hi db042190,

    May I know if you can share the example data of your parameters?

    Please refer to Report Parameters (Report Builder and Report Designer).

    Best Regards,


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Monday, August 10, 2020 5:57 AM
  • thx mona, i can give a few examples.  invoice number is a six digit number.  customer order is a 50 character element.  order number is a 7 character data element.  there are two different kinds of comments that are 50 characters each.

    there are many more elements.   

    Wednesday, August 12, 2020 10:25 AM
  • thx laxmidhar.  are you saying get them once into one hidden param as a comma delimited string and then use ssrs's string splitting capability (i forget the name of the function) to parse them?  wouldnt they then need to be name/pair values?  otherwise i wouldnt know which element unless perhaps i knew them by position.
    Wednesday, August 12, 2020 10:27 AM
  • hi

    yes you can pass like json also . semicolon for key and comma for one value pair to another

    Thanks and regards

    Wednesday, August 12, 2020 11:31 AM
  • thx laxmidhar, can you provide a link or two?  
    Wednesday, August 12, 2020 12:53 PM
  • Hi

    declare @param varchar(100)
    set @param = 'Lorem , ipsum ,dolor ,sit amet'
    SELECT value FROM STRING_SPLIT(@param, ',');

    Thanks and regards

    Thursday, August 13, 2020 7:03 AM