SQL Server function errors trying to run. Throws conversion failed error-CodePudding

I'm not sure why I am getting a conversion issues when executing my function. I don't seem to see where I'm trying to convert from nvarchar to int anywhere.

SELECT * 
FROM [dbo].[Sample_Position]('D84: 0.5 Hr PD')

I get this error:

Msg 245, Level 16, State 1, Line 15
Conversion failed when converting the nvarchar value '0.5' to data type int.

This is my complete code:

CREATE FUNCTION [dbo].[Sample_Position] 
    (@field nvarchar(MAX))
RETURNS @tbl TABLE 
             (
                 POS_ID int NOT NULL IDENTITY(1,1),
                 POS_Type  nvarchar(100),
                 POS_Sort1 nvarchar(100),
                 POS_Sort2 nvarchar(100),
                 POS_Sort3 nvarchar(100)
             ) 
AS
BEGIN
    DECLARE @pos_Type  nvarchar(4000),
            @pos_Sort1 nvarchar(4000),
            @pos_Sort2 nvarchar(4000),
            @pos_Sort3 nvarchar(4000)
    
    BEGIN
        SELECT 
            @pos_Type = CASE   
                            WHEN LEFT(@field, 1) = 'D' THEN 'Day'
                            WHEN LEFT(@field, 1) = 'W' THEN 'Week'
                            WHEN LEFT(@field, 2) = 'GD' THEN 'Gestation Day'
                            WHEN LEFT(@field, 3) = 'PND' THEN 'Prenatal Day'
                            WHEN LEFT(@field, 3) = 'PPD' THEN 'Post Partum Day'
                            WHEN LEFT(@field, 2) = 'BD' THEN 'Birth Day'
                            WHEN LEFT(@field, 3) = 'Phy' THEN 'Physical'
                            WHEN LEFT(@field, 4) = 'UNSC' THEN 'Unscheduled'
                            WHEN LEFT(@field, 5) = 'PreTx' THEN 'Pre Treatment'
                            WHEN LEFT(@field, 3) = 'Imm' THEN 'Immediate'
                            WHEN LEFT(@field, 3) = 'EOI' THEN 'End of Infusion'
                            WHEN LEFT(@field, 3) = 'SOI' THEN 'Start of Infusion'
                            WHEN LEFT(@field, 3) = 'KLH' THEN 'Key Lymphoid'
                            WHEN LEFT(@field, 4) = 'KLHC' THEN 'Key Lymphoid Challenge'
                            WHEN LEFT(@field, 4) = 'PKLH' THEN 'Pre Key Lymphoid'
                            WHEN LEFT(@field, 3) = 'TTC' THEN 'Tetinus Toxoid'
                            WHEN LEFT(@field, 4) = 'PTTC' THEN 'Pre-Tetinus Toxoid'
                            ELSE @field
                        END
          
        SELECT @pos_Sort1 = CASE
                        WHEN @pos_Type = 'Day' then SUBSTRING(@field, PATINDEX('%[0-9]%', @field), PATINDEX('%[0-9][^0-9]%', @field   't') - PATINDEX('%[0-9]%', @field)   1)
                        ELSE 1
                        END
    
        SELECT @pos_Sort2 = CASE
                        WHEN @pos_Type = 'Day' then SUBSTRING(right(@field, len(@field)- PATINDEX('%[ ]%', @field)), PATINDEX('%[0-9.]%', right(@field, len(@field)- PATINDEX('%[ ]%', @field))), PATINDEX('%[0-9.][^0-9.]%', right(@field, len(@field)- PATINDEX('%[ ]%', @field))   't') - PATINDEX('%[0-9.]%', right(@field, len(@field)- PATINDEX('%[ ]%', @field)))   1)
                        ELSE 2
                        END
    
        SELECT @pos_Sort3 = @field
          
        INSERT INTO @tbl (POS_Type, POS_Sort1, POS_Sort2, POS_Sort3)
        VALUES (@pos_Type, @pos_Sort1, @pos_Sort2, @pos_Sort3)
    END

    RETURN
END

CodePudding user response：

If you run the statement below, you will see an output of '0.5' and then a conversion error.

PRINT CASE WHEN 1=1 THEN '0.5' ELSE 1.5 END

PRINT CASE WHEN 1=1 THEN '0.5' ELSE 1 END

This is because SQL Server is inferring the resulting data type based on the literal you coded into your statement.

To get around this return a string literal instead.

PRINT CASE WHEN 1=1 THEN '0.5' ELSE '1' END

CASE statements return data based on data type precedence rules. All possible results are evaluated. The first statement above does not error out because all evaluated results are compatible. In the second statement, the last result can not be converted into the data type of the first possible result.

CodePudding user response：

Your problem, as mentioned, is that one side of your CASE is a varchar, the other an int or numeric. And int has a higher precedence, so the varchar is converted to int and fails.

You can get around this by just putting 1 and 2 into quotes '1' and 2'. Alternatively, you can cast your substring expressions to int, but be careful, as that could fail, and it would be best to use TRY_CAST.

Aside from that, you are using a multi-statement Table Valued Function, which can be slow, especially when run in a correlated APPLY. Instead use an inline TVF.

This also shows how to use CROSS APPLY and virtual tables (SELECT with no FROM, or a VALUES table), and feed them on into the next.

CREATE OR ALTER FUNCTION dbo.Sample_Position
    (@field nvarchar(MAX))
RETURNS TABLE 
AS RETURN

SELECT

  POS_ID = 1,

  v1.pos_Type,

  pos_Sort1 = CASE
              WHEN v1.pos_Type = 'Day' THEN
                   SUBSTRING(
                     @field,
                     v1.FirstDigit,
                     PATINDEX(
                       '%[0-9][^0-9]%',
                       @field   't'
                     ) - v1.FirstDigit   1
                   )
              ELSE
                  '1'
              END,
    
  pos_Sort2 = CASE
              WHEN v1.pos_Type = 'Day' THEN
                SUBSTRING(
                  v2.AfterSpace,
                  v3.DigitAfterSpace,
                  PATINDEX(
                    '%[0-9.][^0-9.]%',
                    v2.AfterSpace   't'
                   ) - v3.DigitAfterSpace   1
                )
              ELSE
               '2'
              END,

  pos_Sort3 = @field

FROM (
    SELECT
      pos_Type =
        CASE   
        WHEN LEFT(@field, 1) = 'D' THEN 'Day'
        WHEN LEFT(@field, 1) = 'W' THEN 'Week'
        WHEN LEFT(@field, 2) = 'GD' THEN 'Gestation Day'
        WHEN LEFT(@field, 3) = 'PND' THEN 'Prenatal Day'
        WHEN LEFT(@field, 3) = 'PPD' THEN 'Post Partum Day'
        WHEN LEFT(@field, 2) = 'BD' THEN 'Birth Day'
        WHEN LEFT(@field, 3) = 'Phy' THEN 'Physical'
        WHEN LEFT(@field, 4) = 'UNSC' THEN 'Unscheduled'
        WHEN LEFT(@field, 5) = 'PreTx' THEN 'Pre Treatment'
        WHEN LEFT(@field, 3) = 'Imm' THEN 'Immediate'
        WHEN LEFT(@field, 3) = 'EOI' THEN 'End of Infusion'
        WHEN LEFT(@field, 3) = 'SOI' THEN 'Start of Infusion'
        WHEN LEFT(@field, 3) = 'KLH' THEN 'Key Lymphoid'
        WHEN LEFT(@field, 4) = 'KLHC' THEN 'Key Lymphoid Challenge'
        WHEN LEFT(@field, 4) = 'PKLH' THEN 'Pre Key Lymphoid'
        WHEN LEFT(@field, 3) = 'TTC' THEN 'Tetinus Toxoid'
        WHEN LEFT(@field, 4) = 'PTTC' THEN 'Pre-Tetinus Toxoid'
        ELSE @field
        END,

    FirstDigit =
      PATINDEX(
        '%[0-9]%',
        @field
      ),

    FirstSpace =
      PATINDEX(
        '%[ ]%',
        @field
      )

) v1

CROSS APPLY (
    SELECT
      AfterSpace =
        right(
          @field,
          len(@field) - v1.FirstSpace
        )
) v2

CROSS APPLY (
    SELECT
      DigitAfterSpace =
        PATINDEX(
          '%[0-9.]%',
          v2.AfterSpace
        )
) v3
;