none
Strange 'Data Conversion' Error RRS feed

  • Question

  • Using SQL Server 2005, I have encountered a very strange error which I cannot figure out.  The simplified stored proc below basically does a select and sorts the records based on the column name given in the @SortCol parameter.  When I enter the field names bapk, jhpk, etc everything works fine.  The records are returned sorted by the field name entered into the parameter.  The first eight variables are  field names and types


    When I enter the field name 'descr' into the @SortCol variable/parameter, I receive the error:


    Msg 295, Level 16, State 3, Procedure CMSCheck2, Line 28
    Conversion failed when converting character string to smalldatetime data type.


    I don't see where this is coming from at all.  The @SortCol variable is always a string.  It contains the name of a field.  The problem is in the  stored proc lines beginning with 'CASE @SortCol'  .If I take out the lines and just use  OVER (Order By descr) as RowNum   -- everything is fine.  If I delete the line  WHEN 'descr' THEN descr  -- everything is fine, it just falls through to the default.


    What is the problem?  Where is a conversion to smalldatetime coming from?   I've been working on this one for a while, and cannot figure it out.  It must be something simple.


     Thanks,

    Mike Thomas
     

    Below is the stored proc and a testing script

     

    TESTING SCRIPT

    USE biz03
    GO

    DECLARE
      @bapk int,
      @jhpk int,
      @transdate smalldatetime,
      @number smallint,
      @descr nvarchar(50),
      @debit decimal(9,2),
      @credit decimal(9,2),
      @cmonth tinyint,
      @StartRowIndex int,
      @MaxRows int,
      @AlphaChar varchar(1),
      @SortCol varchar(20)


    SET @MaxRows = 10
    SET @StartRowIndex = 0
    SET @SortCol = 'descr'
    SET @AlphaChar = ''

    EXEC [dbo].[CMSCheck2]
      @bapk,
      @jhpk,
      @transdate,
      @number,
      @descr,
      @debit,
      @credit,
      @cmonth,
      @StartRowIndex,
      @MaxRows,
      @AlphaChar,
      @SortCol

     

    STORED PROC 

     

     ALTER PROCEDURE [dbo].[CMSCheck2]
      @bapk int,
      @jhpk int,
      @transdate smalldatetime,
      @number smallint,
      @descr nvarchar(50),
      @debit decimal(9,2),
      @credit decimal(9,2),
      @cmonth tinyint,
      @StartRowIndex int,
      @MaxRows int,
      @AlphaChar varchar(1) = null,
      @SortCol varchar(20) = null

    AS
    BEGIN

    SET NOCOUNT ON

    DECLARE @lPaging bit
    IF @AlphaChar is null
      SET @lPaging = 0
    ELSE
      SET @lPaging = 1;

    WITH BankListTemp AS
      (SELECT   bapk, jhpk, transdate, number,
        descr, debit, credit, cmonth, ROW_NUMBER()
        OVER (ORDER BY
        CASE @SortCol
           WHEN 'bapk' THEN bapk
           WHEN 'jhpk' THEN jhpk
           WHEN 'transdate' THEN transdate
           WHEN 'number' THEN number
           WHEN 'descr' THEN descr  -- problem here   --
           WHEN 'debit' THEN debit
           WHEN 'credit' THEN credit
           WHEN 'cmonth' THEN cmonth
           ELSE bapk
        END) as RowNum
      FROM bank)

    SELECT TOP (@MaxRows)    bapk, jhpk, transdate, number,
        descr, debit, credit, cmonth, Rownum
    FROM
     ( SELECT BankListTemp.*,
        (SELECT COUNT(*) FROM BankListTemp) AS RecCount
          FROM BankListtemp) Bank


    END
    Tuesday, April 22, 2008 12:11 PM

Answers

  • The CASE expression returns the data type with the highest precedence. In your CASE expression, the columns have different data types. I guess trandate is of smalldatetime and descr is string. In SQL Server, smalldatetime has higher predence than string (Check BOL for data type precedence). So SQL Server is trying to convert the descr value to smalldatetime, which cause the problem.

     

    You can either split your SP to several code block by the sorting column, using dynamic query, or convert the data explicitly in the CASE expression as shown below:

     

    WITH BankListTemp AS
      (SELECT   bapk, jhpk, transdate, number,
        descr, debit, credit, cmonth, ROW_NUMBER()
        OVER (ORDER BY
        CASE @SortCol
           WHEN 'bapk' THEN bapk
           WHEN 'jhpk' THEN jhpk
           WHEN 'transdate' THEN cast(transdate as nvarchar(100))
           WHEN 'number' THEN number
           WHEN 'descr' THEN descr  -- problem here   --
           WHEN 'debit' THEN debit
           WHEN 'credit' THEN credit
           WHEN 'cmonth' THEN cmonth
           ELSE bapk
        END) as RowNum
      FROM bank)

     

    You may need to convert other columns as well according to their data types

     

    Tuesday, April 22, 2008 1:23 PM
  • The problem is here:

     

    WITH BankListTemp AS
      (SELECT   bapk, jhpk, transdate, number,
        descr, debit, credit, cmonth, ROW_NUMBER()
        OVER (ORDER BY
        CASE @SortCol
           WHEN 'bapk' THEN bapk
           WHEN 'jhpk' THEN jhpk
           WHEN 'transdate' THEN transdate
           WHEN 'number' THEN number
           WHEN 'descr' THEN descr  -- problem here   --
           WHEN 'debit' THEN debit
           WHEN 'credit' THEN credit
           WHEN 'cmonth' THEN cmonth
           ELSE bapk
        END
    ) as RowNum
      FROM bank)

     

    The data type returned by the CASE function will be the one with higher precedence among the types in each WHEN part. Some of the types you are using, can be converted implicitly, but you could get unexpected. In your case, smalldatetime seems to be the one with higher precedence and the rest of types will be converted implicitly, yielding to an error when the data type could not be converted to smalldatetime, like the values on [descr].

     

    You can do the convertion explicitly to make all types the same, like varchar, but keep in mind that character sort does not work as expected for certain values as it does when sorting the natural type. Another approach is to convert all types to sql_variant, but to be honest, I have not used it and can not tell you if there are side effects.

     

     

    AMB

    Tuesday, April 22, 2008 1:26 PM
    Moderator
  •  phe wrote:

    The CASE expression returns the data type with the highest precedence. In your CASE expression, the columns have different data types. I guess trandate is of smalldatetime and descr is string. In SQL Server, smalldatetime has higher predence than string (Check BOL for data type precedence). So SQL Server is trying to convert the descr value to smalldatetime, which cause the problem.

     

    You can either split your SP to several code block by the sorting column, using dynamic query, or convert the data explicitly in the CASE expression as shown below:

     

    WITH BankListTemp AS
      (SELECT   bapk, jhpk, transdate, number,
        descr, debit, credit, cmonth, ROW_NUMBER()
        OVER (ORDER BY
        CASE @SortCol
           WHEN 'bapk' THEN bapk
           WHEN 'jhpk' THEN jhpk
           WHEN 'transdate' THEN cast(transdate as nvarchar(100))
           WHEN 'number' THEN number
           WHEN 'descr' THEN descr  -- problem here   --
           WHEN 'debit' THEN debit
           WHEN 'credit' THEN credit
           WHEN 'cmonth' THEN cmonth
           ELSE bapk
        END) as RowNum
      FROM bank)

     

    You may need to convert other columns as well according to their data types

     

     

    You are right, but the convertion will have to be done in all types with higher precedence than char / varchar, like [debit], [credit], [number], etc.

     

     

    AMB

    Tuesday, April 22, 2008 1:29 PM
    Moderator

All replies

  • The CASE expression returns the data type with the highest precedence. In your CASE expression, the columns have different data types. I guess trandate is of smalldatetime and descr is string. In SQL Server, smalldatetime has higher predence than string (Check BOL for data type precedence). So SQL Server is trying to convert the descr value to smalldatetime, which cause the problem.

     

    You can either split your SP to several code block by the sorting column, using dynamic query, or convert the data explicitly in the CASE expression as shown below:

     

    WITH BankListTemp AS
      (SELECT   bapk, jhpk, transdate, number,
        descr, debit, credit, cmonth, ROW_NUMBER()
        OVER (ORDER BY
        CASE @SortCol
           WHEN 'bapk' THEN bapk
           WHEN 'jhpk' THEN jhpk
           WHEN 'transdate' THEN cast(transdate as nvarchar(100))
           WHEN 'number' THEN number
           WHEN 'descr' THEN descr  -- problem here   --
           WHEN 'debit' THEN debit
           WHEN 'credit' THEN credit
           WHEN 'cmonth' THEN cmonth
           ELSE bapk
        END) as RowNum
      FROM bank)

     

    You may need to convert other columns as well according to their data types

     

    Tuesday, April 22, 2008 1:23 PM
  • The problem is here:

     

    WITH BankListTemp AS
      (SELECT   bapk, jhpk, transdate, number,
        descr, debit, credit, cmonth, ROW_NUMBER()
        OVER (ORDER BY
        CASE @SortCol
           WHEN 'bapk' THEN bapk
           WHEN 'jhpk' THEN jhpk
           WHEN 'transdate' THEN transdate
           WHEN 'number' THEN number
           WHEN 'descr' THEN descr  -- problem here   --
           WHEN 'debit' THEN debit
           WHEN 'credit' THEN credit
           WHEN 'cmonth' THEN cmonth
           ELSE bapk
        END
    ) as RowNum
      FROM bank)

     

    The data type returned by the CASE function will be the one with higher precedence among the types in each WHEN part. Some of the types you are using, can be converted implicitly, but you could get unexpected. In your case, smalldatetime seems to be the one with higher precedence and the rest of types will be converted implicitly, yielding to an error when the data type could not be converted to smalldatetime, like the values on [descr].

     

    You can do the convertion explicitly to make all types the same, like varchar, but keep in mind that character sort does not work as expected for certain values as it does when sorting the natural type. Another approach is to convert all types to sql_variant, but to be honest, I have not used it and can not tell you if there are side effects.

     

     

    AMB

    Tuesday, April 22, 2008 1:26 PM
    Moderator
  •  phe wrote:

    The CASE expression returns the data type with the highest precedence. In your CASE expression, the columns have different data types. I guess trandate is of smalldatetime and descr is string. In SQL Server, smalldatetime has higher predence than string (Check BOL for data type precedence). So SQL Server is trying to convert the descr value to smalldatetime, which cause the problem.

     

    You can either split your SP to several code block by the sorting column, using dynamic query, or convert the data explicitly in the CASE expression as shown below:

     

    WITH BankListTemp AS
      (SELECT   bapk, jhpk, transdate, number,
        descr, debit, credit, cmonth, ROW_NUMBER()
        OVER (ORDER BY
        CASE @SortCol
           WHEN 'bapk' THEN bapk
           WHEN 'jhpk' THEN jhpk
           WHEN 'transdate' THEN cast(transdate as nvarchar(100))
           WHEN 'number' THEN number
           WHEN 'descr' THEN descr  -- problem here   --
           WHEN 'debit' THEN debit
           WHEN 'credit' THEN credit
           WHEN 'cmonth' THEN cmonth
           ELSE bapk
        END) as RowNum
      FROM bank)

     

    You may need to convert other columns as well according to their data types

     

     

    You are right, but the convertion will have to be done in all types with higher precedence than char / varchar, like [debit], [credit], [number], etc.

     

     

    AMB

    Tuesday, April 22, 2008 1:29 PM
    Moderator
  • Many thanks for your answers.  I need to think about this one a little - but I think I am starting to see the logic.  The CASE looks at the data types of the columns underlying the variable @SortCol, then takes the one with the highest precedence; smalldatetime, over int and varchar.  It sorts the int columns OK, that works with  a smalldatetime type, but int throws an exception.  I'll need to de a little playing around to really grasp the concept.

    Thanks
    Mike Thomas
    Tuesday, April 22, 2008 8:28 PM