Can i create a table from my parameters?

• jeffshelix

  SSCrazy

  Points: 2257

  More actions

  May 7, 2019 at 3:56 pm

  #3637228

  If yes, any links to instructions would be great.

  I am often times asked to search for parts, and their associated data (price, seller, buyer, size, etc. in our database.

  note: I use SSMS, but then I load the reports into SSRS/SharePoint for the end user.

  I am given a list of, for example, 100 parts.  I have reports where the user can insert their parts into a parameter and run the report.  Usually, they get 100 results. But sometimes we don't have those parts in our system yet.  I would like to have my SQL show me the few parts that were not found.

  Can I, somehow, put  100 parts from a parameter in SSRS into a table in my SQL, then left join on it?  Or any other way of advising the user that these 5 parts were not in inventory???

• Thom A

  SSC Guru

  Points: 99143

  More actions

  May 7, 2019 at 5:49 pm

  #3637286

  You could by switching to a Stored Procedure. SSRS, when using syntax like WHERE [Column Name] IN (@Parameter) injects the values into the query instead, replacing the value of @Parameter with a delimited list of string literals. If, however, you switch to using a Stored Procedure it supplies a delimited string literal; so instead of 'Value1','Value2','Value3' it passes 'Value1,Value2,Value3'. This has the advantage of being parametrised, but also means you can then use that as your source table.

  Using delimitedsplit8k_LEAD you can then change your query, in the SP, to something like:

  SELECT DS.item, {Your other Columns}
  FROM dbo.delimitedsplit8k_LEAD (@Parameter,',') DS
       LEFT JOIN YourTable YT ON DS.Item = YT.{Column}
  WHERE ...

   

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• jeffshelix

  SSCrazy

  Points: 2257

  More actions

  May 7, 2019 at 7:58 pm

  #3637392

  Thanks for the ideas, much appreciated!