I got into yet another issue with my SQL optimizations this week at work and one of them was that I needed to pass a list of IDs to a function that would be then used in a SQL statement via an “in” clause. The problem is that the list argument is a varchar:
Then I needed to use it in my SQL statement like this:
The problem with this approach is that the datatype for the docType column was tinyint and then the argument is a varchar list, so it would fail as it cannot cast it. Bummer! So what to do? Well, after some searching and searching, the best idea so far (brought up by our local dev guru Sanjib Panda), was to break the list into a simple table with one column of type tinyint, but how? Here is the simple logic which could easily be created as a UDF:
So if you run the above, that will give you our temp table with one docType column with all the values we needed. Man, sometimes I wish SQL could have some ColdFusion goodness and create a cool function like: listToTable() or something. Anyways, after splitting the list into a table then I can change my SQL to something like this:
There you go! The solution is working really fast and given us a nice little split to rows function that can be used on any variable. Hope this helps.
After much trying out and pains, what I had did not work fully :( So here is an updated function: