1

I've been tasked with creating a report for my company. The report is generated from the results returned by the Stored Procedure spGenerateReport, which has multiple filters.

Inside the SP, this is how the filter is expected to work:

SELECT * FROM MyTable WHERE column1 IN (
    'filters', 'for', 'this', 'report'
)

Entering the code above yields ~30000 rows in 9s. However, I want to be able to change my SP's filter by passing it a single argument (since I may use 1 or 2 or n filters), like so:

spGenerateReport 'Filters,for,this,report'

For this I have the User-Created Function fnSplitString (yes, I do know that there is a STRING_SPLIT function but I can't use it due to a lower compatibility level of my database) which splits a single string into a table, like so:

SELECT splitData FROM fnSplitString('Filters,for,this,report')

Returns:

splitData
------
Filters
for
this
report

Thus the final code in my SP is:

SELECT * FROM MyTable WHERE column1 IN (
    SELECT * FROM fnSplitString('Filters,for,this,report')
)

However, this instead yields ~10000 rows in 60s. The time taken to complete this SP is weird but isn't too much of a problem, however nearly a quarter of my rows disappearing into the void certainly is. The results only have rows from the first couple filters (for example, 'Filters' and 'for'; if I change the order of the arguments (e.g.: fnSplitString('report,for,Filters,this')), I get a different number of rows, and only from filters 'report', 'for', 'Filters'! I don't understand why using the function returns different results than those obtained when using the literal strings. Is there some inside gimmick that I'm not aware of?

PS - I'm sorry in advance for being bad at explaining myself, and for any grammar mistakes

SatXa
  • 13
  • 4
  • Perhaps you should look into a full text index, rather than doing all this string manipulation. – Gordon Linoff Nov 15 '19 at 11:32
  • There may be something wrong with `fnSplitString` and/or the argument types you're passing into the function and/or any other manipulations on the argument you pass in that cause the strings to be silently truncated. – Jeroen Mostert Nov 15 '19 at 11:33
  • You definitely should be getting the same results. Is there a max-length on any of the varchars that fnSplitString consumers or emits? It looks like something is getting truncated somewhere. – codeulike Nov 15 '19 at 11:35
  • 1
    I think your fnSplitString function is not outputting the last string in the list. Post the code of the function. – codeulike Nov 15 '19 at 11:36
  • Here is a stringsplit function for pre-SQL2016 if you want to use one that definitely works right: https://stackoverflow.com/a/512300/22194 – codeulike Nov 15 '19 at 11:38

1 Answers1

1

You should definitely be getting the same results with both techniques. Something is wrong.

You havent posted the fnSplitString code but I suspect fnSplitString is not outputting the last string in the list, or maybe the last string in the list is being truncated before it reaches fnSplitString so that no matches are found.

e.g. if the parameter going into your spGenerateReport stored procedure is varchar(20) then what will reach the function is 'Filters,for,this,rep' with the last bit truncated.

SSRS, for example, will truncate strings that are being passed into an SP instead of warning you with an error message

codeulike
  • 22,514
  • 29
  • 120
  • 167
  • And if you're doing multi-value parameters with SSRS (sounds like you might be) here's a guide to all the steps - https://stackoverflow.com/a/9862901/22194 – codeulike Nov 15 '19 at 11:52
  • This was, indeed, the case. I noticed just before coming to read your answer: my SP took a VARCHAR(50) as an argument and my actual filter was 117 characters long. I have thus expanded it to VARCHAR(500) and is now working correctly. Turns out it actually was an inside gimmick that I was not aware of (that arguments with greater lengths are truncated and do not throw any errors), thank you for making me aware of this. With any luck I will learn from this to be more careful from now on. Thank you! – SatXa Nov 15 '19 at 12:09
  • Ah yes, SSRS silently truncating is indeed an inside gimmick – codeulike Nov 15 '19 at 12:34