May 7, 2019 at 3:56 pm
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???
May 7, 2019 at 5:49 pm
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
May 7, 2019 at 7:58 pm
Thanks for the ideas, much appreciated!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply