The problem is that even when no record is found, the value of @EquipPK is set to 0 (default) in the following SQL Select

select @EquipPk = EQUIP_PK from EQ_FILE where EQUIP_ID = 'BOGUSID'

I should have not assigned the default value to the @EquipPk. My mistake.

>In that case my original code should have worked - it checks for value not being NULL or empty.
>
>You can add
>
>PRINT 'Equpment Id = ' + coalesce(@EquipmentId,'');
>
>to be sure it's passed
>
>>The @EqupmentId is a char(15) type.
>>
>>>What is the type of the @EqupmentId variable? If it's integer, then you may want
>>>
>>>if COALESCE(@EquipmentId, 0) !=0 -- this tests for value being not null and not 0
>>>
>>>
>>>
>>>>>Why does it have to be one line?
>>>>>

>>>>>if COALESCE(@EquipmentId,'') !=''
>>>>>   begin
>>>>>   select @Pk = equip_pk from dbo.eq_file where Id_Number = @EquipmentId;
>>>>>   if @Pk==null
>>>>>             begin
>>>>>                 raieserror ...
>>>>>                 return 
>>>>>             end
>>>>>     end

>>>>
>>>>I don't know how COALESCE() works but in my test, when I pass a value to @EquipmentId, the code never goes to SELECT ..