If condition inside stored procedure

  • Dear friends,

    I am using two queries inside a stored procedure to check two different tables. I have used if condition. How do i check whether the condition satisfying...

    Thanks in advance

    Procedure

    =========

    ALTER Proc [dbo].[CsaWise Cust List]

    @Reg_Code Char(3),@Csa_Code char(5)

    ,@Pickup_Date datetime

    as

    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 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 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

    )

    /*

    --Exec [CsaWise Cust List1] 'MAS','D0111','N','2009-03-02'

    create Proc [dbo].[CsaWise Cust List1]

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

    ,@Csa_Code char(5),@Pickup_Date datetime

    as

    if @typ='S'

    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 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 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

    else

    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 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

    )

    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

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply