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)

     

    Verified Answer?

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

    by:Posted 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

    This content is available to Experts Exchange members

    See the answer now
    with your Free 30 Day Trial

    Get unlimited access to solutions & experts

    • 4,169,477 solved questions
    • 3,805 articles & videos
    • 15,413 tech experts

    Get Access Now

    Ask Your Tech Question. Get Expert Solutions.We will email you when an expert has commented on your question.

    We will never share this with anyone. Privacy Policy Terms of Use

    Select topics

    You may select up to five topics.

    Top Expert Contributor

    Essential articles and videos from the Experts

    More valuable questions with Expert answers

    201508-LO-Qu-072

    RELATED TOPICS view all topics

    1. MS SQL Server
      (152,802)
    2. MS SQL Server 2005
      (71,307)
    3. Query Syntax
      (45,497)
    4. SSRS
      (8,313)
    5. Databases
      (52,115)
    6. Windows Server 2008
      (77,760)
    7. ASP.NET
      (121,168)
    8. .NET Programming
      (129,741)
    9. C#
      (89,510)
    10. MS Development
      (48,488)