Following SQL Server function works fine with simple examples but when it is passed a column that has millions of records, it gives the error shown below:

Question: What could be a cause of the problem, and it can be fixed?

Function:

Create FUNCTION MyFunction(@var varchar(300))
RETURNS varchar(300)
AS
BEGIN

    IF LEN(ISNULL(@var, '')) = 0
        return @var

    declare @varLen tinyint = LEN(@var)

    SET @var = CASE
    WHEN LEFT(@var,4) = 'abc ' THEN RIGHT(@var,@varLen-4)
    WHEN LEFT(@var,4) = 'abc.' THEN RIGHT(@var,@varLen-4)
    WHEN LEFT(@var,3) = 'rs ' THEN RIGHT(@var,@varLen-3)
    WHEN LEFT(@var,4) = 'rs. ' THEN RIGHT(@var,@varLen-4)
    ELSE @var
    END

    RETURN @var
END

Simple query [correctly returns the expected output]:

declare @t varchar(150) = 'abc ewotiu ryire'
select dbo.TestFunction(@t)  --returns "ewotiu ryire"

Sample Query that gives an error:

select myColumn MyFunction(myColumn) from myTable

REMARKS:

1. myColumn has millions of records, has no null values, there are about 10 empty values (blanks ''). But I have taken care of that scenario in the first line of the code above. I have tested that when I pass null or blank string, it correctly returns null or empty string.
2. I have also tried using DATALENGTH function instead of LEN, but still the exact same error
3. Using Azure SQL Db: Microsoft SQL Azure (RTM) - 12.0.2000.8 Sep 18 2021 19:01:34 Copyright (C) 2019 Microsoft Corporation
4. Online search (such as this or this etc.) did not help - probably my issue is a bit different having to do with millions of records or something else.

Error: [When passed to a query with million of records ]

Msg 536, Level 16, State 2 Invalid length parameter passed to the RIGHT function.

CodePudding user response：

You will see this error if you pass 'abc ' or 'rs ' to your function as LEN does not count trailing spaces.

So for 'rs ' the expression RIGHT(@var,@varLen-3) ends up as RIGHT('rs ',2-3) and -1 is an invalid length.

You can use the following instead to just specify the desired start position directly instead of calculating it with LEN

CREATE OR ALTER FUNCTION MyFunction(@var VARCHAR(300))
RETURNS VARCHAR(300)
AS
  BEGIN
      RETURN CASE WHEN @var LIKE 'abc %' OR @var LIKE 'abc.%' OR @var LIKE 'rs. %' THEN SUBSTRING(@var, 5, 300)
               WHEN @var LIKE 'rs %' THEN SUBSTRING(@var, 4, 300)
               ELSE @var
             END
  END 

The reason for switching to LIKE is so that trailing spaces also become significant in the string comparison part. Otherwise 'rs' and 'rs ' will also compare equal (which would also give the error in your original function and needs to be guarded against to give the desired results in the rewrite).

CodePudding user response：

to illustrate your problem:

declare @a varchar(300)
set @a = 'abc'
if LEFT(@a,4) = 'abc '
  select LEN(@a)-4

-----------
-1

(1 row(s) affected)

To illustrate one more problem in your code

declare @a varchar(300)
set @a = REPLICATE('A',300)

declare @varLen tinyint = LEN(@a)

Msg 220, Level 16, State 2, Line 4
Arithmetic overflow error for data type tinyint, value = 300.