Use the print statement to cross verfiy your condition

and

check the alternate for your SP

create Proc [dbo].[CsaWise Cust List1]

@Reg_Code Char(3),@typ char(1)

,@Csa_Code char(5),@Pickup_Date datetime

as

BEGIN

select csa_cd,con_cd,con_name,br_cd,pktime From Opendatasource('SQLOLEDB','DATA SOURCE=FFCLTN-DC\SQLEXPRESS;User ID=sa;password=fttnsql').Pickup.dbo.Cons

where csa_cd=@Csa_Code and cregion=@Reg_Code

and

(( @typ='S' AND con_cd not in

(

SELECT m_con_cd FROM

OPENROWSET('vfpoledb.1','d:\WebProjBackUps\CargoSms\dbfs\';'';'','select bking_date,m_con_cd,m_emp_cd from air_mast')

AS o where o.bking_date= convert(char(10),@Pickup_Date,101)

and o.m_emp_cd=@Csa_Code group by m_con_cd

)) OR (SELECT m_con_cd FROM

OPENROWSET('vfpoledb.1','d:\WebProjBackUps\CargoSms\dbfs\';'';'','select bking_date,m_con_cd,m_emp_cd from sur_mast')

AS o where o.bking_date= convert(char(10),@Pickup_Date,101)

and o.m_emp_cd=@Csa_Code group by m_con_cd

))

and con_cd not in

(

select con_cd from CsaPupMissing a where convert(char(10),a.pickup_date,101)=convert(char(10),@Pickup_Date,101) and a.csa_code=@Csa_Code

)

END