Using Text Query Is Producing Argument Errors For Functions

Question

I am using a text query for my data set. I am attempting to take a text variable called @AssignmentStatuses which should have a comma delimited value like '1,2,3,4,5,6' and I'm trying to split these values into separate rows for a table. Here is the entire query for this data set:

CREATE TABLE #PerformanceCodeList
(
 PerformanceCodeID INT
);

WHILE LEN(@AssignmentStatuses) = 1
  BEGIN
    INSERT #PerformanceCodeList
    (
      PerformanceCodeID
    )
    SELECT TRIM(SUBSTRING(@AssignmentStatuses, 0, CASE WHEN CHARINDEX(',', @AssignmentStatuses, 0) = 0
                                                            THEN LEN (@AssignmentStatuses) + 1
                                                       ELSE CHARINDEX(',', @AssignmentStatuses, 0)
                                                  END));

    SELECT @AssignmentStatuses = SUBSTRING(@AssignmentStatuses, CASE WHEN CHARINDEX(',', @AssignmentStatuses, 0) = 0
                                                                          THEN LEN(@AssignmentStatuses) + 1
                                                                     ELSE CHARINDEX(',', @AssignmentStatuses, 0)
                                                                END + 1, LEN(@AssignmentStatuses));
  END

SELECT *
FROM #PerformanceCodeList

When running the report with the above code, I'm getting the following errors:

The len function requires 1 argument(s). The charindex function requires 2 to 3 arguments. The charindex function requires 2 to 3 arguments.

Yet all of my LEN functions have only one argument, and my CHARINDEX functions have 3 arguments.

Answer 1

This is definitely a bug, no doubt about that. But here's a work around and I'm embarrassed I didn't realize this sooner.

What I was essentially trying to do was to take a multi-value text parameter (containing integer values) and parse it into a temp table. I would then use this temp table that would have integer values as a filter using INNER JOIN to my data to produce my desired results. This works great in a stored procedure, but for some reason is causing errors in the text query feature. I had excluded this code from the question in an attempt to simplify the problem, and the code below is nowhere near as complex as what I'm actually doing, but should get the point across.

SELECT *  
FROM dbo.SomeTable AS st  
INNER JOIN #PerformanceCodeList AS pcl ON st.PerformanceCodeID = pcl.PerformanceCodeID  

I had previously attempted to use a different solution where I attempted to parse the parameter in the INNER JOIN code as well as in the WHERE clause. Parsing seems to be what is causing the errors in the text query feature.

The solution is to filter using the IN operator but converting the integer value to a varchar value first.

SELECT *  
FROM dbo.SomeTable AS st  
WHERE TRY_CONVERT ( VARCHAR ( 25 ), st.PerformanceCodeID ) IN ( @AssignmentStatuses )  

Thank you @Joyzhao-MSFT , even though you weren't able to provide an answer I appreciate you for attempting to help me figure this out and for being my rubber duck (https://en.wikipedia.org/wiki/Rubber_duck_debugging).

Answer 2

Hi @Bryan Diercks ，
Regarding SQL statements, please post a consultation on the TSQL forum (sql-server-transact-sql). There you will get more expert help.
Best Regards,
Joy