Link to home
Start Free TrialLog in
Avatar of Glen_D
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)
Avatar of Lotok
Lotok
Flag of United Kingdom of Great Britain and Northern Ireland image

Why do you need it set as a varchar?
Could you not use:


@Program tinyint = -1,

Open in new window

Avatar of Pratima
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
Avatar of Glen_D
Glen_D

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
Avatar of Pratima
Pratima
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Glen_D

ASKER

wow...that looks so simple; by quoting the -1 values, I'll prevent SQL from automatically trying to convert?

Thx