Q

Question

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)

201409-LO-Qu-014

Find Your Answer,
Ask the Experts.

Start your free trial

Free 30 day trial of a Premium Membership. Cancel anytime.

What's a Premium Membership?

Download the Experts Exchange Guide

Consulting & Contracting:
How to Go Out On Your Own

Can you become an independent contractor with your own business?

We will never share this with anyone.

Skill Training Video TutorialsExpand your tech skills quickly with five-minute, instructional videos.

See More Database Videos

What does a Premium Membership get you?

  • Unlimited Access to over 4 million verified technology solutions

  • Unlimited Access to thousands of interactive articles and videos

  • Unlimited technical writing assistance

  • Submit your projects for Expert Testing

  • Expand your professional network with our messaging and hire me features

  • Ad free and mobile optimized experience

Join the Network Today!

See Plans and Pricing