Error converting data type varchar to bigint in stored procedure
I am trying to call this procedure using a command usp_TimesheetsAuditsLoadAllbyId 42747, NULL
.
But I always get the error
Msg 8114, Level 16, State 5, Procedure usp_TimesheetsAuditsLoadAllById, Line 9
Error converting varchar to bigint data type.
A ID
table table TimesheetsAudits
is a type bigint
. I've tried several types of conversions and castings, but I'm really stuck right now.
Hope someone can help. thank
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
Defining a table 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
)
source to share
You are doing INNER JOIN [dbo]. [TimesheetsAudits] and TimesheetItems ti ON tia.ReferrerId = ti.ID
tia. [ReferrerId] - varchar and ti. [ID] - [bigint].
I am expecting a value in tia. [ReferrerId] which cannot be converted to bigint.
Try the following:
SELECT [ReferrerId] FROM TimesheetItemsAudits WHERE ISNUMERIC(ReferrerId) = 0
This can help you find the "offensive strings".
source to share