Solved

Conversion failed when converting the varchar value '-1"' to data type int.

Posted on 2009-03-06
1,069 Views
Last Modified: 2012-06-21
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)
Good Question?
0
Question by:Glen_D
    1,921 Solutions
    Best Solution byPratima Pharande
    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,
    5 Comments
     
    99 Solutions

    Expert Comment

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


    @Program tinyint = -1,
    

    Open in new window

    Good Comment?
    0
     
    1,921 Solutions

    Expert Comment

    by:Pratima Pharande
    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
    Good Comment?
    0
     
    5 Solutions

    Author Comment

    by:Glen_D
    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.
    Good Comment?
    0
     
    1,921 Solutions

    Accepted Solution

    by:
    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)
     
    Tags:
    MS SQL 2005/2008
    Good Solution?
    0
     
    5 Solutions

    Author Comment

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

    Thx
    Good Comment?
    0

    Add a Comment

    Join our community to follow up on this question and 4 million more solutions.

    Join & Write a Comment

    Featured Post

    Course: AWS Professional Certification

    These classes are designed to help you pass the AWS Certified Solutions Architect – Associate, AWS Certified Developer, and AWS Certified SysOps Administrator Associate exams—all essential to a career as an AWS professional.

    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This video Micro Tutorial is the first in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 (http://www.experts-exchange.com/articles/17490/). But the ability to create custom scanning profiles al…
    This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

    771 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    31 Experts available now in Live!

    Get 1:1 Help Now