Using Text Query Is Producing Argument Errors For Functions

Bryan Diercks 6 Reputation points
2022-01-31T18:58:55.877+00:00

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.

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,793 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Bryan Diercks 6 Reputation points
    2022-02-02T16:48:41.137+00:00

    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).

    1 person found this answer helpful.
    0 comments No comments

  2. Joyzhao-MSFT 15,566 Reputation points
    2022-02-01T02:05:00.083+00:00

    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