Glen_D
asked on
Conversion failed when converting the varchar value '-1"' to data type int.
My CF developer couldn't pass me NULLS so we decided on -1 (a value) that would never present itself in our db. I tested the sp with the -1 values and everything worked well but when she called the sp, received the following error:
Conversion failed when converting the varchar value '-1"' to data type int.
A portion of my code is below...
Thx
ALTER PROCEDURE [dbo].[usp_Search2]
@Program varchar (50) = -1,
@Project varchar (255) = NULL,
@Center varchar (50) = NULL,
@Facility_Name varchar (255)= NULL,
@BuildNos varchar (255) = NULL,
SELECT
f.MainID,
p.Program,
p.Project,
p.Center,
f.Facility_Name,
f.Building_Nos,
From...
WHERE
(ISNULL(@Program,-1) = -1 or p.Program = @Program)
and (ISNULL (@Project, -1)= -1 or p.Project = @Project)
and (ISNULL (@Center,-1) = -1 or p.Center = @Center)
and (ISNULL (@Facility_Name,-1)= -1 or f.Facility_Name = @Facility_Name)
and (ISNULL (@BuildNos, -1)= -1 or f.Building_Nos = @BuildNos)
Conversion failed when converting the varchar value '-1"' to data type int.
A portion of my code is below...
Thx
ALTER PROCEDURE [dbo].[usp_Search2]
@Program varchar (50) = -1,
@Project varchar (255) = NULL,
@Center varchar (50) = NULL,
@Facility_Name varchar (255)= NULL,
@BuildNos varchar (255) = NULL,
SELECT
f.MainID,
p.Program,
p.Project,
p.Center,
f.Facility_Name,
f.Building_Nos,
From...
WHERE
(ISNULL(@Program,-1) = -1 or p.Program = @Program)
and (ISNULL (@Project, -1)= -1 or p.Project = @Project)
and (ISNULL (@Center,-1) = -1 or p.Center = @Center)
and (ISNULL (@Facility_Name,-1)= -1 or f.Facility_Name = @Facility_Name)
and (ISNULL (@BuildNos, -1)= -1 or f.Building_Nos = @BuildNos)
Check whther all have datatype varchar
p.Program,
p.Project,
p.Center,
f.Facility_Name,
f.Building_Nos,
any one of this field amy have datatype int
p.Program,
p.Project,
p.Center,
f.Facility_Name,
f.Building_Nos,
any one of this field amy have datatype int
ASKER
The input from the front screen (for the parameters) comes from lookup tables in varchar format; my default value was NULL but CF has an issue with NULLS so we decied to use the -1. I checked all my fields and everything is set to varchar; SQL is trying to convert the -1 to an int.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
wow...that looks so simple; by quoting the -1 values, I'll prevent SQL from automatically trying to convert?
Thx
Thx
Could you not use:
Open in new window