gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-01 10:46:52 25 4
gpt4 key购买 nike

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

但我总是收到错误

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


IDTimesheetsAudits表是 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/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com