Error converting data type varchar to bigint in stored procedure
41,131
You perform an INNER JOIN of [dbo].[TimesheetsAudits] and TimesheetItems ti ON tia.ReferrerId = ti.ID
tia.[ReferrerId] is varchar and ti.[ID] is [bigint].
I'd expect a value in tia.[ReferrerId] that cannot be converted to bigint.
Try the following:
SELECT [ReferrerId] FROM TimesheetItemsAudits WHERE ISNUMERIC(ReferrerId) = 0
This may help you to find the "offending rows".
Author by
davser
Updated on August 12, 2020Comments
-
davser over 3 years
I'm trying to call this procedure with the
usp_TimesheetsAuditsLoadAllbyId 42747, NULL
command.But I always get an error
Msg 8114, Level 16, State 5, Procedure usp_TimesheetsAuditsLoadAllById, Line 9
Error converting data type varchar to bigint.The
ID
ofTimesheetsAudits
table is abigint
type. I tried several types of conversions and casts, but I'm really stuck right now.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 )
-
DCaugs about 7 yearsA word of caution for other googlers: Be careful with ISNUMERIC(), as as it will also validate to true on both scientific notation as well as some currency symbols. More on that here: msdn.microsoft.com/en-us/library/ms186272.aspx. You may be able to use TRY_CAST() (introduced in SQL 2012) to vet your conversions in cases where you have to deal with these elements.