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

    Question by:
    On

    Topics:

    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
     

    ?

    The member who asked this question verified this comment provided the solution that solved their problem.

    Accepted Solution on 2009-03-06 at 02:00:38ID: 23815185

    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

    Top Expert Contributor

    Essential articles and videos from the Experts

    More valuable questions with Expert answers

    201511-LO-Qu-074

    Extend your technology team with the Experts Exchange community.

    — trusted by —

    Who answers my questions?Our community has technology experts around the world.

    Vitor Montalvão

    11

    Articles

    Expert in:

    • MS SQL Server
    • MS SQL Server 2008
    • MS SQL Server 2005
    • Query Syntax
    • Databases

    David Todd

    Expert in:

    • MS SQL Server
    • MS SQL Server 2005
    • MS SQL Server 2008
    • Databases
    • Query Syntax

    Brian Crowe

    Expert in:

    • MS SQL Server
    • MS SQL Server 2008
    • Visual Basic.NET
    • Query Syntax
    • Crystal Reports

    Jim Horn

    18

    Articles

    Expert in:

    • MS Access
    • MS SQL Server
    • MS SQL Server 2008
    • MS SQL Server 2005
    • Query Syntax

    Paul Maxwell

    9

    Articles

    Expert in:

    • MS SQL Server
    • MS SQL Server 2008
    • Query Syntax
    • MS SQL Server 2005
    • Oracle Database

    Robert Schutt

    Expert in:

    • JavaScript
    • .NET Programming
    • Visual Basic.NET
    • ASP.NET
    • C#

    Koen Van Wielink

    Expert in:

    • MS SQL Server
    • MS SQL Server 2008
    • Query Syntax
    • SSRS

    PadawanDBA

    Expert in:

    • MS SQL Server
    • MS SQL Server 2008

    geek_vj

    Expert in:

    • MS SQL Server
    • MS SQL Server 2005
    • MS SQL Server 2008

    RELATED TOPICS view all topics

    1. MS SQL Server
      (154,655)
    2. MS SQL Server 2005
      (71,588)
    3. Query Syntax
      (46,075)
    4. SSRS
      (8,512)
    5. Databases
      (52,991)
    6. Windows Server 2008
      (79,881)
    7. ASP.NET
      (122,079)
    8. .NET Programming
      (131,084)
    9. C#
      (90,384)
    10. MS Development
      (48,689)