SQL 在存储过程中将数据类型 varchar 转换为 bigint 时出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25251848/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error converting data type varchar to bigint in stored procedure
提问by davser
I'm trying to call this procedure with the usp_TimesheetsAuditsLoadAllbyId 42747, NULLcommand.
我正在尝试使用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.
消息 8114，级别 16，状态 5，过程 usp_TimesheetsAuditsLoadAllById，第 9 行将
数据类型 varchar 转换为 bigint 时出错。
The IDof TimesheetsAuditstable is a biginttype. I tried several types of conversions and casts, but I'm really stuck right now.
所述ID的TimesheetsAudits表是一个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
)
回答by Lmu92
You perform an INNER JOIN of [dbo].[TimesheetsAudits] and TimesheetItems ti ON tia.ReferrerId = ti.ID
您执行 [dbo].[TimesheetsAudits] 和 TimesheetItems ti ON tia.ReferrerId = ti.ID 的 INNER JOIN
tia.[ReferrerId] is varchar and ti.[ID] is [bigint].
tia.[ReferrerId] 是 varchar，ti.[ID] 是 [bigint]。
I'd expect a value in tia.[ReferrerId] that cannot be converted to bigint.
我希望 tia.[ReferrerId] 中的值不能转换为 bigint。
Try the following:
请尝试以下操作：
SELECT [ReferrerId] FROM TimesheetItemsAudits WHERE ISNUMERIC(ReferrerId) = 0
This may help you to find the "offending rows".
这可能会帮助您找到“违规行”。
