sql - 在存储过程中将数据类型 varchar 转换为 bigint 时出错

我正在尝试使用 usp_TimesheetsAuditsLoadAllbyId 42747, NULL 命令调用此过程。

但我总是收到错误


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

希望有人能帮忙。谢谢

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

来自评论的表的表定义:

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
)

最佳答案

您执行 [dbo].[TimesheetsAudits] 和 TimesheetItems ti ON tia.ReferrerId = ti.ID 的 INNER JOIN

tia.[ReferrerId] 是 varchar，ti.[ID] 是 [bigint]。

我希望 tia.[ReferrerId] 中的值不能转换为 bigint。

请尝试以下操作:

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

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

关于sql - 在存储过程中将数据类型 varchar 转换为 bigint 时出错，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25251848/