SQL存储过程只返回由函数创建的第一行表。

I have a function which converts a delimited VARCHAR into a table of INTs.

我有一个函数，它将一个分隔的VARCHAR转换成一个INTs表。

IntSplit:

USE [master]
GO

/****** Object:  UserDefinedFunction [dbo].[IntSplit]    Script Date: 02/10/2016 15:17:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[IntSplit]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Data' = CONVERT(INT, SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos))
    FROM Split
)

GO

When I run this in a query:

当我在查询中运行这个时:

SELECT * FROM IntSplit('1,2,3', ',')

I get the expected result of:

我得到的预期结果是:

 |Data
-+----
1| 1
2| 2
3| 3

I also have a stored procedure that does exactly the same thing (i.e. calls the function)

我还有一个存储过程，它做的事情完全相同(例如调用函数)

my_int_split:

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[my_int_split]    Script Date: 02/10/2016 15:29:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[my_int_split]
(@i_ids VARCHAR)
AS
    -- Common set conditions
    -- Explicity suppress count information
    SET NOCOUNT ON
    -- Unhandled exception aborts whole transaction
    SET XACT_ABORT ON
    -- Autocommit on
    SET IMPLICIT_TRANSACTIONS OFF
    -- UK datetime
    SET DATEFORMAT dmy
    --
    SET NOCOUNT ON
    --
    BEGIN
        SELECT * FROM IntSplit(@i_ids, ',')
    END

GO

However, if I run the stored procedure with

但是，如果我运行存储过程

my_int_split '1,2,3'

then I only get the first row back as the result

然后我只得到第一行作为结果

 |Data
-+----
1| 1

As far as I can tell the stored procedure is doing EXACTLY THE SAME THING as the query, but only ever returns the top row. Note that if I put in a VARCHAR of '2,3,4' it will return a single row of value 2, so it's not just always spitting 1 back out.

就我所知，存储过程与查询执行的操作完全相同，但只返回第一行。注意，如果我输入一个VARCHAR(2,3,4)它会返回一行值2，所以它并不总是把1吐出来。

Any thoughts?

任何想法吗?

1 个解决方案

#1

Never ever use VARCHAR without a length, your @i_ids parameter is being truncated to 1 character.

永远不要使用没有长度的VARCHAR，您的@i_ids参数将被截断为1个字符。

If you know how long the list of IDs may become, use this as a guide to specify the max length (plus some slack); otherwise use VARCHAR(MAX) which is less efficient but pretty much arbitrary in size.

如果您知道id列表的长度，可以使用它作为指定最大长度(加上一些松弛)的指南;否则使用VARCHAR(MAX)，效率较低，但在大小上几乎是任意的。

Also, avoid to mix NVARCHAR (in the function) and VARCHAR (in the SP) - doing so can cause performance degregation due to conversions. Also (while not applicable in this specific case) it can cause indexes to be skipped because the data type is not identical.

此外，避免将NVARCHAR(在函数中)和VARCHAR(在SP中)混合使用——这样做会由于转换而导致性能下降。而且(虽然在这种情况下不适用)它会导致索引被跳过，因为数据类型不相同。

The "funny" thing is that the length assumed when no length is given is not consistent. See Aaron Bertrand's blog post for more details on this.

有趣的是，当没有给定长度时，假设的长度是不一致的。参见Aaron Bertrand的博客文章，了解更多细节。

#1