March 2, 2009 at 1:06 am
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
March 2, 2009 at 4:49 am
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