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...
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,
(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)