TSQL Table Value Function Parameter different behavior if parameter a variable

Question

I have a table value function in TSQL that works well if I pass a string but not if I pass a varchar.

For example, this works:

SELECT * from [dbo].[SplitString]('ta;aa;qq', ';')

enter image description here

This does not work:

declare @invarchar as varchar
set @invarchar = 'ta;aa;qq'
SELECT * from [dbo].[SplitString](@invarchar, ';')

enter image description here

If you are curious about the function:

CREATE FUNCTION [dbo].[SplitString]
    (
        @List NVARCHAR(MAX),
        @Delim VARCHAR(255)
    )
    RETURNS TABLE
    AS
        RETURN ( SELECT [Value] FROM 
          ( 
            SELECT 
              [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
              CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
            FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
              FROM sys.all_objects) AS x
              WHERE Number <= LEN(@List)
              AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
          ) AS y
        );
GO

I have the same result with multiple table value function, so I guess that my problem is in the way I call it from the variable that is wrong. How can I use a variable and still have the same output?

Community · Accepted Answer · 2020-07-29 18:26:20Z

2

The default length of VARCHAR when declared without a length is 1 or sometimes 30. In your scenario, it defaults to 1. That's why you're only seeing 1 row with t as a result. You should declare @invarchar as VARCHAR(MAX), or simply add a length.

DECLARE @invarchar as `VARCHAR(MAX)`

See this article by Aaron Bertrand for more information.

edited Jul 29, 2020 at 18:26

CommunityBot

11 silver badge

answered Jun 11, 2015 at 1:11

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

TSQL Table Value Function Parameter different behavior if parameter a variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related