在存储过程中将数据类型varchar转换为bigint的错误

I'm trying to call this procedure with the usp_TimesheetsAuditsLoadAllbyId 42747, NULL command.

我尝试使用usp_TimesheetsAuditsLoadAllbyId 42747, NULL命令调用这个过程。

But I always get an error

但我总是会出错

Msg 8114, Level 16, State 5, Procedure usp_TimesheetsAuditsLoadAllById, Line 9
Error converting data type varchar to bigint.

Msg 8114，级别16，状态5，过程usp_TimesheetsAuditsLoadAllById，第9行错误地将数据类型varchar转换为bigint。

The ID of TimesheetsAudits table is a bigint type. I tried several types of conversions and casts, but I'm really stuck right now.

工时表的ID是bigint类型。我尝试了几种类型的转换和强制转换，但是我现在真的被困住了。

Hope somebody can help. Thanks

希望有人能帮助。谢谢

ALTER PROCEDURE [dbo].[usp_TimesheetsAuditsLoadAllById]
(
    @Id INT,
    @StartDate DATETIME
)
AS
BEGIN
   SET NOCOUNT ON

   SELECT TOP 51 * 
   FROM 
      (SELECT TOP 51 
          ID,
          Type, 
          ReferrerId,
          CAST(Description AS VARCHAR(MAX)) AS Description,
          OnBehalfOf,
          Creator,
          DateCreated 
       FROM 
          TimesheetsAudits 
       WHERE 
          (ReferrerID = @Id) AND
          (@StartDate IS NULL OR DateCreated < @StartDate)
       ORDER BY
          DateCreated DESC

       UNION

       SELECT TOP 51 
          tia.ID,
          tia.Type, 
          tia.ReferrerId,
          '[Day: ' + CAST(DayNr AS VARCHAR(5)) + '] ' + CAST(tia.Description AS VARCHAR(MAX)) AS Description,
          tia.OnBehalfOf,
          tia.Creator,
          tia.DateCreated 
       FROM 
          TimesheetItemsAudits tia
       INNER JOIN 
          TimesheetItems ti ON tia.ReferrerId = ti.ID
       WHERE 
          (ti.TimesheetID = @Id) AND
          (@StartDate IS NULL OR tia.DateCreated < @StartDate)
       ORDER BY 
          tia.DateCreated DESC) t
   ORDER BY 
       t.DateCreated DESC
END

Table definition for tables from comments:

表定义:

CREATE TABLE [dbo].[TimesheetsAudits]( 
  [ID] [bigint] IDENTITY(1,1) NOT NULL, 
  [Type] [tinyint] NOT NULL, 
  [ReferrerId] [varchar](15) NOT NULL, 
  [Description] [text] NULL, 
  [OnBehalfOf] [varchar](10) NULL, 
  [Creator] [varchar](10) NOT NULL, 
  [DateCreated] [datetime] NOT NULL
)



CREATE TABLE [dbo].[TimesheetItemsAudits]( 
  [ID] [bigint] IDENTITY(1,1) NOT NULL, 
  [Type] [tinyint] NOT NULL, 
  [ReferrerId] [varchar](15) NOT NULL, 
  [Description] [text] NULL, 
  [OnBehalfOf] [varchar](10) NULL, 
  [Creator] [varchar](10) NOT NULL, 
  [DateCreated] [datetime] NOT NULL
)

1 个解决方案

#1

You perform an INNER JOIN of [dbo].[TimesheetsAudits] and TimesheetItems ti ON tia.ReferrerId = ti.ID

您执行[dbo]的内部连接。[工时表审核]和工时表审核。ReferrerId = ti.ID

tia.[ReferrerId] is varchar and ti.[ID] is [bigint].

tia。[ReferrerId]是varchar和ti。长整型数字(ID)。

I'd expect a value in tia.[ReferrerId] that cannot be converted to bigint.

我希望它有价值。[ReferrerId]不能转换为bigint。

Try the following:

试试以下:

SELECT [ReferrerId] FROM TimesheetItemsAudits WHERE ISNUMERIC(ReferrerId) = 0

This may help you to find the "offending rows".

这可以帮助您找到“违规行”。

#1