Stored Procedure If Statement not working/executin

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure If Statement not working/executin

Author

Topic

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2009-01-15 : 10:09:04

[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

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
[/code]

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.

Any help would be great.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-01-15 : 11:21:01

Perhaps the data is like that? The only diff I see between the two queries is "AspNum not in (2,4)" part..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2009-01-15 : 11:27:02

I did a print debug and 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?

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2009-01-15 : 14:19:05

After lots of hair pulling I learned the problem was downstream data.

My apologies.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources