Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stored Procedure If Statement not working/executing

Status
Not open for further replies.
mxfrail

mxfrail

MIS
Jul 7, 2008
25
0
0
US
CREATE PROCEDURE ASP_StatusReport

@SalesRep int,
@Range int,
@All int

AS

If @All = 1
Begin

SELECT tblclients.clientid, tblclients.dbaname, tblcontractdetail.psijobno as PSIJobNo, convert(varchar(10),tblContractDetail.InHomeDate, 101) as InHomeDate,
convert(varchar(10),dbo.ASP_DueDate(min(tblcontractdetail.proddate), tblcontractdetail.franchiseid, Case When tblcontractDetail.artrecd = 1 and (tblcontractdetail.procnum = 0 or tblcontractdetail.procnum is null) then 2 else tbljobprocessmappings.aspnum end, tblcontractdetail.mailingno), 101) as DueDate, Case When tblcontractDetail.artrecd = 1 and tblcontractdetail.procnum = 0 then 2 else tbljobprocessmappings.aspnum end as AspNum

FROM (tblClients INNER JOIN (tblContractDetail INNER JOIN
tblContracts ON tblContractDetail.ContractID = tblContracts.ContractID) ON
tblClients.ClientID = tblContracts.ClientID) Left JOIN tblJobProcessMappings ON
tblContractDetail.ProcNum = TblJobProcessMappings.ProcNum

WHERE tblcontractdetail.repid = @SalesRep and tblcontractdetail.status <> 'Invoiced' and tblContractDetail.InHomeDate between convert(varchar(10), getdate(), 101)
and convert(varchar(10),DateAdd(Day, @Range, getdate()), 101)

GROUP BY tblcontractdetail.psijobno, tblclients.clientid, tblclients.dbaname, tblcontractdetail.mailingno, tblcontractdetail.franchiseid, tbljobprocessmappings.aspnum, tblContractDetail.InHomeDate, tbljobprocessmappings.aspnum, tblcontractdetail.artrecd, tblcontractdetail.procnum
Order BY duedate asc, tblclients.dbaname

End

ELSE

Begin

SELECT tblclients.clientid, tblclients.dbaname, tblcontractdetail.psijobno as PSIJobNo, convert(varchar(10),tblContractDetail.InHomeDate, 101) as InHomeDate,
convert(varchar(10),dbo.ASP_DueDate(min(tblcontractdetail.proddate), tblcontractdetail.franchiseid, Case When tblcontractDetail.artrecd = 1 and (tblcontractdetail.procnum = 0 or tblcontractdetail.procnum is null) then 2 else tbljobprocessmappings.aspnum end, tblcontractdetail.mailingno), 101) as DueDate, Case When tblcontractDetail.artrecd = 1 and tblcontractdetail.procnum = 0 then 2 else tbljobprocessmappings.aspnum end as AspNum

FROM (tblClients INNER JOIN (tblContractDetail INNER JOIN
tblContracts ON tblContractDetail.ContractID = tblContracts.ContractID) ON
tblClients.ClientID = tblContracts.ClientID) left JOIN tblJobProcessMappings ON
tblContractDetail.ProcNum = TblJobProcessMappings.ProcNum

WHERE tblcontractdetail.repid = @SalesRep and tblcontractdetail.status <> 'Invoiced' and tblContractDetail.InHomeDate between convert(varchar(10), getdate(), 101)
and convert(varchar(10),DateAdd(Day, @Range, getdate()), 101) and AspNum not in (2,4)

GROUP BY tblcontractdetail.psijobno, tblclients.clientid, tblclients.dbaname, tblcontractdetail.mailingno, tblcontractdetail.franchiseid, tbljobprocessmappings.aspnum, tblContractDetail.InHomeDate, tbljobprocessmappings.aspnum, tblcontractdetail.artrecd, tblcontractdetail.procnum
Order BY duedate asc, tblclients.dbaname

End
GO



If I run the select statements individually manually put in numbers it works fine. But if I run it as say -

exec asp_statusreport @SalesRep=191, @All=1, @Range=60
or
exec asp_statusreport @SalesRep=191, @All=0, @Range=60

It runs the same values as if the If @All=1 portion of if statement is not working as if the IF statement is not even running and it just runs the first select statement.

Any help would be great.
 
Sort by date Sort by votes
No way, put a debugging print in both and see what is executed:
Code: 
....

IF @All = 1
   BEGIN
      PRINT '@All = 1'
      SELECT .....
   END
ELSE
   BEGIN
      PRINT '@All <> 1'
      SELECT .....
   END
....


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Upvote 0 Downvote
I think your problem occurs because you are left joining the tblJobProcessMappings table, but you also have a where clause condition (AspNum Not In (2,4)). To fix this, you can put that condition on the join clause. Like this...

Code: 
CREATE PROCEDURE ASP_StatusReport

@SalesRep int,
@Range int,
@All int

 AS

If @All = 1
Begin

SELECT tblclients.clientid, tblclients.dbaname, tblcontractdetail.psijobno as PSIJobNo, convert(varchar(10),tblContractDetail.InHomeDate, 101) as InHomeDate,
    convert(varchar(10),dbo.ASP_DueDate(min(tblcontractdetail.proddate), tblcontractdetail.franchiseid, Case When tblcontractDetail.artrecd = 1 and (tblcontractdetail.procnum = 0 or tblcontractdetail.procnum is null) then 2 else tbljobprocessmappings.aspnum end, tblcontractdetail.mailingno), 101) as DueDate, Case When tblcontractDetail.artrecd = 1 and tblcontractdetail.procnum = 0 then 2 else tbljobprocessmappings.aspnum end as AspNum

FROM (tblClients INNER JOIN (tblContractDetail INNER JOIN 
    tblContracts ON tblContractDetail.ContractID = tblContracts.ContractID) ON 
    tblClients.ClientID = tblContracts.ClientID) Left JOIN tblJobProcessMappings ON 
    tblContractDetail.ProcNum = TblJobProcessMappings.ProcNum

WHERE tblcontractdetail.repid = @SalesRep and tblcontractdetail.status <> 'Invoiced' and  tblContractDetail.InHomeDate between  convert(varchar(10), getdate(), 101) 
and convert(varchar(10),DateAdd(Day, @Range, getdate()), 101)

GROUP BY tblcontractdetail.psijobno, tblclients.clientid,  tblclients.dbaname, tblcontractdetail.mailingno, tblcontractdetail.franchiseid, tbljobprocessmappings.aspnum, tblContractDetail.InHomeDate, tbljobprocessmappings.aspnum, tblcontractdetail.artrecd, tblcontractdetail.procnum
Order BY duedate asc, tblclients.dbaname

End

ELSE

Begin

SELECT tblclients.clientid, tblclients.dbaname, tblcontractdetail.psijobno as PSIJobNo, convert(varchar(10),tblContractDetail.InHomeDate, 101) as InHomeDate,
    convert(varchar(10),dbo.ASP_DueDate(min(tblcontractdetail.proddate), tblcontractdetail.franchiseid, Case When tblcontractDetail.artrecd = 1 and (tblcontractdetail.procnum = 0 or tblcontractdetail.procnum is null) then 2 else tbljobprocessmappings.aspnum end, tblcontractdetail.mailingno), 101) as DueDate, Case When tblcontractDetail.artrecd = 1 and tblcontractdetail.procnum = 0 then 2 else tbljobprocessmappings.aspnum end as AspNum

FROM (tblClients INNER JOIN (tblContractDetail INNER JOIN 
    tblContracts ON tblContractDetail.ContractID = tblContracts.ContractID) ON 
    tblClients.ClientID = tblContracts.ClientID) left JOIN tblJobProcessMappings ON 
    tblContractDetail.ProcNum = TblJobProcessMappings.ProcNum
    [!]and AspNum not in (2,4)[/!]

WHERE tblcontractdetail.repid = @SalesRep and tblcontractdetail.status <> 'Invoiced' and  tblContractDetail.InHomeDate between  convert(varchar(10), getdate(), 101) 
and convert(varchar(10),DateAdd(Day, @Range, getdate()), 101) 

GROUP BY tblcontractdetail.psijobno, tblclients.clientid,  tblclients.dbaname, tblcontractdetail.mailingno, tblcontractdetail.franchiseid, tbljobprocessmappings.aspnum, tblContractDetail.InHomeDate, tbljobprocessmappings.aspnum, tblcontractdetail.artrecd, tblcontractdetail.procnum
Order BY duedate asc, tblclients.dbaname

End

Note that I moved that condition from the where clause to the Join/On clause. Try this, if it works for you, and you would like me to explain, let me know.




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I did that i guess it is going into the if / else statements.

But if i run each select statement separately I get 37 rows if (@all would be 1) and 1 if (@all would be 0).

But if stored proc runs it returns 37 rows no matter what.

I assume the problem is here -


In Select of @all <> 1

Case When tblcontractDetail.artrecd = 1 and tblcontractdetail.procnum = 0 then 2 else tbljobprocessmappings.aspnum end as AspNum

and in my where clause -

and AspNum not in (2,4)

Is there a way I can do that portion better?
 
Upvote 0 Downvote
I tried moving the portion of the join clause and same data result is returned for both if running the stored proc.
 
Upvote 0 Downvote
Did you also remove it from the Where clause?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
try to run both queries in separate SSMS or QA Window and check what both returns:
Code: 
SELECT 'ALL=1' AS Test, tblclients.clientid,
       tblclients.dbaname,
       tblcontractdetail.psijobno as PSIJobNo, 
       convert(varchar(10),tblContractDetail.InHomeDate, 101) as InHomeDate,
       convert(varchar(10),dbo.ASP_DueDate(min(tblcontractdetail.proddate), tblcontractdetail.franchiseid,
       Case When tblcontractDetail.artrecd = 1 and (tblcontractdetail.procnum = 0 or tblcontractdetail.procnum is null) then 2 else tbljobprocessmappings.aspnum end, tblcontractdetail.mailingno), 101) as DueDate, Case When tblcontractDetail.artrecd = 1 and tblcontractdetail.procnum = 0 then 2 else tbljobprocessmappings.aspnum end as AspNum

FROM (tblClients INNER JOIN (tblContractDetail INNER JOIN
    tblContracts ON tblContractDetail.ContractID = tblContracts.ContractID) ON
    tblClients.ClientID = tblContracts.ClientID) Left JOIN tblJobProcessMappings ON
    tblContractDetail.ProcNum = TblJobProcessMappings.ProcNum

WHERE tblcontractdetail.repid = @SalesRep and tblcontractdetail.status <> 'Invoiced' and  tblContractDetail.InHomeDate between  convert(varchar(10), getdate(), 101)
and convert(varchar(10),DateAdd(Day, @Range, getdate()), 101)

GROUP BY tblcontractdetail.psijobno, tblclients.clientid,  tblclients.dbaname, tblcontractdetail.mailingno, tblcontractdetail.franchiseid, tbljobprocessmappings.aspnum, tblContractDetail.InHomeDate, tbljobprocessmappings.aspnum, tblcontractdetail.artrecd, tblcontractdetail.procnum
Order BY duedate asc, tblclients.dbaname

--- I used George's query here.
SELECT 'ALL <> 1' AS Test, tblclients.clientid, tblclients.dbaname, tblcontractdetail.psijobno as PSIJobNo, convert(varchar(10),tblContractDetail.InHomeDate, 101) as InHomeDate,
    convert(varchar(10),dbo.ASP_DueDate(min(tblcontractdetail.proddate), tblcontractdetail.franchiseid, Case When tblcontractDetail.artrecd = 1 and (tblcontractdetail.procnum = 0 or tblcontractdetail.procnum is null) then 2 else tbljobprocessmappings.aspnum end, tblcontractdetail.mailingno), 101) as DueDate, Case When tblcontractDetail.artrecd = 1 and tblcontractdetail.procnum = 0 then 2 else tbljobprocessmappings.aspnum end as AspNum

FROM (tblClients INNER JOIN (tblContractDetail INNER JOIN
    tblContracts ON tblContractDetail.ContractID = tblContracts.ContractID) ON
    tblClients.ClientID = tblContracts.ClientID) left JOIN tblJobProcessMappings ON
    tblContractDetail.ProcNum = TblJobProcessMappings.ProcNum
    and AspNum not in (2,4)

WHERE tblcontractdetail.repid = @SalesRep and tblcontractdetail.status <> 'Invoiced' and  tblContractDetail.InHomeDate between  convert(varchar(10), getdate(), 101)
and convert(varchar(10),DateAdd(Day, @Range, getdate()), 101)

GROUP BY tblcontractdetail.psijobno, tblclients.clientid,  tblclients.dbaname, tblcontractdetail.mailingno, tblcontractdetail.franchiseid, tbljobprocessmappings.aspnum, tblContractDetail.InHomeDate, tbljobprocessmappings.aspnum, tblcontractdetail.artrecd, tblcontractdetail.procnum
Order BY duedate asc, tblclients.dbaname

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
Create Function Within IF statement. 1
Replies
4
Views
190
mikrom
mikrom
Cpreston
  • Locked
  • Question
Working Days giving NULL values
Replies
8
Views
115
Cpreston
Cpreston
grnzbra
  • Locked
  • Question
Is &quot;IF EXISTS()...&quot; better than &quot;IF (SELECT COUNT...) &gt; 0&quot;
Replies
4
Views
749
gmmastros
gmmastros
ahmedsa2018
  • Locked
  • Question
How to give same number if it exist on table or new number if it not exist based on part value? 1
Replies
8
Views
69
Andrzejek
Andrzejek
Colin Burton
  • Locked
  • Question
Determine if a file exists on s different server to the SQL Server 1
Replies
2
Views
184
gmmastros
gmmastros

Part and Inventory Search

Sponsor

Back
Top