Table row row limit?

Question

My application has a user-defined function that takes a comma-separated list as an argument. It splits the elements and connects them to the table variable and returns the result.

This function works well, except that when items in a comma-delimited list exceed 1000, it ignores the remainder. That is, if I connect 1239, the first 1000 lines will be returned, and the remaining 239 will be completely ignored. There are no errors.

I cannot help but feel that this is due to some kind of restriction that I should be aware of, but I cannot find any information about this. Is this a limit on the number of rows that can be stored in a table variable? Or am I missing something in the code itself? Can anyone help? Come here with an airless look.

ALTER FUNCTION [dbo].[ufnConvertArrayToIntTable] (@IntArray VARCHAR(8000)) RETURNS @retIntTable TABLE ( ID int ) AS BEGIN DECLARE @Delimiter char(1) SET @Delimiter = ',' DECLARE @Item varchar(8) IF CHARINDEX(@Delimiter,@IntArray,0) <> 0 BEGIN WHILE CHARINDEX(@Delimiter,@IntArray,0) <> 0 BEGIN SELECT @Item = RTRIM(LTRIM(SUBSTRING(@IntArray,1,CHARINDEX(@Delimiter,@IntArray,0)-1))), @IntArray = RTRIM(LTRIM(SUBSTRING(@IntArray,CHARINDEX(@Delimiter,@IntArray,0)+1,LEN(@IntArray)))) IF LEN(@Item) > 0 INSERT INTO @retIntTable SELECT @Item END IF LEN(@IntArray) > 0 INSERT INTO @retIntTable SELECT @IntArray END ELSE BEGIN IF LEN(@IntArray) > 0 INSERT INTO @retIntTable SELECT @IntArray END RETURN END; 

Answer 1

You define your input variable as varchar (8000) and your @Item variable as varchar (8). Are your items usually 8 characters? Is the string you send with over 1000 elements over 8000 characters? Instead, try changing your input to varchar (max).

Answer 2

Are all your comma-separated values ​​8 characters long? If so, then the input parameter can only hold 888 (8000/9 (including the comma)).

Answer 3

This is because your input parameter is limited to 8000 characters.

You can try calling the function using a substring ... Perhaps:

 WHERE [myField] IN(Select ID from [dbo].[ufnConvertArrayToIntTable](substring(@inputarray, 1, 4000)) OR [myField] IN(Select ID from [dbo].[ufnConvertArrayToIntTable](substring(@inputarray, 4001, 8000)) ...