表变量行限制? [英] Table variable row limitation?

问题描述

我的应用程序中有一个用户定义的函数，它将逗号分隔的列表作为参数.它拆分项目并将它们插入到表变量中并返回结果.

I have in my application a user defined function which takes a comma separated list as an argument. It splits the items and plugs them in to a table variable and returns the result.

这个函数效果很好，只是当逗号分隔列表中的项目超过1000时，它会忽略余数.也就是说，如果我插入1239，将返回前1000行，其余239行完全忽略.发生这种情况时没有错误.

This function works well, except that when the items in the comma separated list exceed 1000, it ignores the remainder. That is to say, if I plug in 1239, the first 1000 rows will be returned and the remaining 239 are entirely ignored. There are no errors when this occurs.

我不禁觉得这是由于某种我应该知道的限制造成的，但我似乎找不到任何有关它的信息.是否限制了可以存储在表变量中的行数?或者我在实际代码本身中遗漏了什么?任何人都可以提供帮助吗?呆呆地看着这边.

I can't help but feel that this is due to some sort of limitation that I should know about, but I can't seem to find any information about it. Is it a limitation on the amount of rows that can be stored in a table variable? Or am I missing something in the actual code itself? Can anyone assist? Going squirrely-eyed over here.

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;

推荐答案

您将输入变量定义为 varchar(8000)，@Item 变量为 varchar(8).您的项目通常每个是 8 个字符吗?您发送的超过 1000 个项目的字符串是否超过 8000 个字符?尝试将输入更改为 varchar(max).

You define your input variable as varchar(8000) and your @Item variable is varchar(8). Are your items typically 8 characters each? Is the string you send in w/ over 1000 items more than 8000 characters? Try changing your input to varchar(max) instead.

这篇关于表变量行限制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！