Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

If condition inside stored procedure Expand / Collapse
Author
Message
Posted Monday, March 2, 2009 1:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, April 17, 2010 2:12 AM
Points: 12, Visits: 63
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
Post #666393
Posted Monday, March 2, 2009 4:49 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 6:47 PM
Points: 459, Visits: 184
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
Post #666475
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse