IsNull() Works in Query but not in Stored Procedur

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

Other SQL Server Topics (2005)

IsNull() Works in Query but not in Stored Procedur

Author

Topic

fjstratman
Starting Member

5 Posts

Posted - 2007-05-29 : 15:05:22

Hi,

I'm using IsNULL(<insert something SQL witty here>) to zero out a derived column if there's no return result. When I submit my query in Management Console the results come back perfectly, when I send it over as a stored procedure (as an alter or create) then execute, my last few columns come back 0 not populated as in the query run. Anyone know why this happens?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-05-29 : 15:35:59

Post the code, please...

e4 d5 xd5 Nf6

fjstratman
Starting Member

5 Posts

Posted - 2007-05-29 : 15:50:29

DECLARE
@FirstCutHalf int,
@FirstCutBeginDate datetime,
@FirstCutEndDate datetime,
@SecondCutHalf int,
@SecondCutBeginDate datetime,
@SecondCutEndDate datetime,
@CurrentWeekNumber int,
@CurrentWeekBegDate datetime,
@CurrentWeekEndDate datetime,
@PreviousWeekNumber int,
@PreviousWeekBegDate datetime,
@PreviousWeekEndDate datetime

SELECT @FirstCutHalf = HalfIndicator, @FirstCutBeginDate = BegDate, @FirstCutEndDate = EndDate
FROM Pipeline_CutoffDates where Status = 'Current'

SELECT @SecondCutHalf = HalfIndicator, @SecondCutBeginDate = BegDate, @SecondCutEndDate = EndDate
FROM Pipeline_CutoffDates where Status = 'Next'

SELECT @CurrentWeekNumber = weeknumber, @CurrentWeekBegDate = BegDate, @CurrentWeekEndDate = EndDate
FROM pipeline_weekdates WHERE weeknumber = datepart(wk,getdate())

SELECT @PreviousWeekNumber = weeknumber, @PreviousWeekBegDate = begdate, @PreviousWeekEndDate = enddate
FROM pipeline_weekdates WHERE weeknumber = datepart(wk,getdate()) - 1

SELECT a.FullName,
a.LoanOfficer,
IsNull((SELECT count(UID)
from pipeline_borrower pb
where pb.loanofficer = a.loanofficer
and ClosingDate between @FirstCutBeginDate and @SecondCutEndDate),0) as 'Number of Units',
IsNull((SELECT convert(decimal(10,2),sum(brokerfee+processingfee+yieldspread))
from pipeline_borrower pb
where pb.loanofficer = a.loanofficer
AND ClosingDate between @FirstCutBeginDate and @SecondCutEndDate),0) as 'MTD Projected Fees',
IsNull((SELECT convert(decimal(10,2),sum(brokerfee+processingfee+yieldspread))
from pipeline_borrower pb
where pb.loanofficer = a.loanofficer
AND ClosingDate between @FirstCutBeginDate and @SecondCutEndDate and LoanIsClosed = 1),0) as 'Closed',
IsNull((SELECT convert(decimal(10,2),sum(brokerfee+processingfee+yieldspread))
from pipeline_borrower pb1
where pb1.loanofficer = a.loanofficer
AND ClosingDate between @FirstCutBeginDate and @SecondCutEndDate)
-
(SELECT convert(decimal(10,2),sum(brokerfee+processingfee+yieldspread))
from pipeline_borrower pb2
where pb2.loanofficer = a.loanofficer
AND ClosingDate between @FirstCutBeginDate and @SecondCutEndDate and LoanIsClosed = 1),0) as '+/-',
IsNull((select count(*)
from pipeline_borrower pb
where Datesigned between @CurrentWeekBegDate and @CurrentWeekEndDate
and pb.loanofficer = a.loanofficer),0) as 'New Pipe Units',
IsNull((select convert(decimal(10,2),sum(brokerfee+processingfee+yieldspread))
from pipeline_borrower pb
where DateSigned BETWEEN @CurrentWeekBegDate and @CurrentWeekEndDate
and pb.loanofficer = a.loanofficer),0) as 'New Pipe Fees',
IsNull((select count(*)
from pipeline_borrower pb
where ClosingDate between @FirstCutBeginDate and @SecondCutEndDate
and pb.loanofficer = a.loanofficer
and DateSigned < @CurrentWeekBegDate),0) as 'Old Pipe Units',
IsNull((Select convert(decimal(10,2),sum(brokerfee+processingfee+yieldspread))
from pipeline_borrower pb
where ClosingDate BETWEEN @FirstCutBeginDate and @SecondCutEndDate
and pb.loanofficer = a.loanofficer
and DateSigned < @CurrentWeekBegDate),0) as 'Old Pipe Fees'
FROM Pipeline_LoanOfficers a
WHERE a.Role = 'Loan Officer'
ORDER BY 'Closed' DESC

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-05-29 : 16:49:36

Avoid using subquerys in your SELECT clause. Join the subqueries instead. And do yourself a favor and dump the aliases, so that your code and logic are readable. "a", and "pb", say explain nothing about the data contenct. For example, change this syntax:


SELECT   a.FullName,
         a.LoanOfficer,
         IsNull((SELECT count(UID)
                 from   pipeline_borrower pb
                 where  pb.loanofficer = a.loanofficer
                        and ClosingDate between @FirstCutBeginDate
                                                and @SecondCutEndDate),
                0) as 'Number of Units',
         IsNull((Select convert(decimal(10,2),sum(brokerfee + processingfee + yieldspread))
                 from   pipeline_borrower pb
                 where  ClosingDate BETWEEN @FirstCutBeginDate
                                            and @SecondCutEndDate
                        and pb.loanofficer = a.loanofficer
                        and DateSigned < @CurrentWeekBegDate),
                0) as 'Old Pipe Fees'
FROM     Pipeline_LoanOfficers a
WHERE    a.Role = 'Loan Officer'
ORDER BY 'Closed' DESC

...to this syntax:


SELECT	Pipeline_LoanOfficers.FullName,
	Pipeline_LoanOfficers.LoanOfficer,
	IsNull(NumUnits.UnitCount, 0) as 'Number of Units',
	IsNull(PipeFees.PipeFeeTotal, 0) as 'Old Pipe Fees'
FROM	Pipeline_LoanOfficers
	left outer join  --LoanOfficerIDS
		(SELECT	loanofficer,
			count(UID) as UnitCount
                from	pipeline_borrower pb
                where	ClosingDate between @FirstCutBeginDate and @SecondCutEndDate
		group by loanofficer) NumUnits
		on NumUnits.loanofficer = Pipeline_LoanOfficers.loanofficer
	left outer join --PipeFees
		(Select	loanofficer,
			sum(brokerfee + processingfee + yieldspread) PipeFeeTotal
                 from	pipeline_borrower
                 where	ClosingDate BETWEEN @FirstCutBeginDate and @SecondCutEndDate
			and DateSigned < @CurrentWeekBegDate
		group by loanofficer) PipeFees
		on PipeFees.loanofficer = Pipeline_LoanOfficers.loanofficer
WHERE    Pipeline_LoanOfficers.Role = 'Loan Officer'
ORDER BY 'Closed' DESC

e4 d5 xd5 Nf6

fjstratman
Starting Member

5 Posts

Posted - 2007-05-29 : 18:16:24

Ok, so for my +/- on the difference, how can I convert that into a join to make it easier to deal with, the others I understand..... Appreciate the help immensely.... F

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-05-29 : 18:55:29

I'm afraid I don't understand your question. The example I gave does use joins.

e4 d5 xd5 Nf6

fjstratman
Starting Member

5 Posts

Posted - 2007-05-29 : 19:11:27

In my original query, there is a difference subselect for projected minus closed, that is still going to require a subselect, how should I attack it?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-05-30 : 00:41:03

[code]
SELECT Pipeline_LoanOfficers.FullName,
Pipeline_LoanOfficers.LoanOfficer,
IsNull(PlusCalc.PlusValue, 0) - IsNull(MinusCalc.MinusValue, 0) as 'PlusMinus'
FROM Pipeline_LoanOfficers
left outer join --PlusCalc
(SELECT loanofficer,
convert(decimal(10,2),sum(brokerfee + processingfee + yieldspread)) as PlusValue
from pipeline_borrower
where ClosingDate between @FirstCutBeginDate
and @SecondCutEndDate
group by loanofficer) PlusCalc
on Pipeline_LoanOfficers.loanofficer = PlusCalc.loanofficer
left outer join --MinusCalc
(SELECT loanofficer,
convert(decimal(10,2),sum(brokerfee + processingfee + yieldspread)) as MinusValue
from pipeline_borrower
where ClosingDate between @FirstCutBeginDate
and @SecondCutEndDate
and LoanIsClosed = 1
group by loanofficer) MinusCalc
on Pipeline_LoanOfficers.loanofficer = MinusCalc.loanofficer
WHERE Pipeline_LoanOfficers.Role = 'Loan Officer'
ORDER BY 'Closed' DESC
[/code]
...but wouldn't this logic work just as well?:
[code]
(SELECT loanofficer,
convert(decimal(10,2),sum(brokerfee + processingfee + yieldspread)) as PlusMinusValue
from pipeline_borrower
where ClosingDate between @FirstCutBeginDate
and @SecondCutEndDate
and LoanIsClosed <> 1
group by loanofficer) PlusMinusCalc
[/code]
...and exactly what are you expecting to accomplish with [code]ORDER BY 'Closed' DESC[/code]?

e4 d5 xd5 Nf6

fjstratman
Starting Member

5 Posts

Posted - 2007-05-30 : 13:58:23

It's an odd application. The Order By 'Closed' sorts the result by most business closed first, a .NET page catches the results and colors the most biz closed over 40K dollars as gold.

I figured out the difference piece, what is shows is the projected fees minus what is closed, if they're over great, if they're under, not so great.

The scenario is this... You have current month's track of business (# of units, Fees), then how much was projected/forecasted and what has actually closed, then you have new business (what's been done this week), and old business (what's been prior to this week). The page that pulls this has options to say what threshholds are allowed (must have at least 30K in fees in the pipeline, must have at least 15K closed at all times, must have 10K in new business each week). While it seems illogical, it performs pretty well in keeping sales moving.

And here is where the dates and weeks are whacked, in the lending business you have recission periods, and in order to keep cash flowing in the company, you cutoff the closing on the 25th of the month to allow for recission if any and to make sure your people are credited commission before the end of the month. It slides from month to month because you always want your last day to close to coincide with the last business day, hence the moving target date fields.

This is what I ended up with to get the report results...
Thank you again for your assistance.
SELECT Pipeline_LoanOfficers.FullName,
Pipeline_LoanOfficers.LoanOfficer,
IsNull(NumUnits.UnitCount, 0) as 'Number of Units',
IsNull(ProjFees.TotalFees, 0) as 'MTD Projected Fees',
IsNull(ClosedFees.TotalFees,0) as 'Closed',
IsNull((ProjFees.TotalFees - ClosedFees.TotalFees),0) as '+/-',
IsNull(SignedUnits.UnitCount,0) as 'New Pipe Units',
IsNull(NewPipeFees.TotalFees,0) as 'New Pipe Fees',
IsNull(OldPipeUnits.UnitCount,0) as 'Old Pipe Units',
IsNull(PipeFees.PipeFeeTotal, 0) as 'Old Pipe Fees'
FROM Pipeline_LoanOfficers
left outer join --LoanOfficerIDS
(SELECT loanofficer,
count(UID) as UnitCount
from pipeline_borrower pb
where ClosingDate between @FirstCutBeginDate and @SecondCutEndDate
group by loanofficer) NumUnits
on NumUnits.loanofficer = Pipeline_LoanOfficers.loanofficer
left outer join --MTDProjectedFees
(SELECT loanofficer, sum(brokerfee+processingfee+yieldspread) as TotalFees
from pipeline_borrower
where ClosingDate BETWEEN @FirstCutBeginDAte and @SecondCutEndDate
group by loanofficer) ProjFees
on ProjFees.loanofficer = Pipeline_LoanOfficers.loanofficer
left outer join --Closed
(SELECT loanofficer, sum(brokerfee+processingfee+yieldspread) as TotalFees
from pipeline_borrower
where ClosingDate BETWEEN @FirstCutBeginDAte and @SecondCutEndDate and LoanIsClosed=1
group by loanofficer) ClosedFees
on ClosedFees.loanofficer = Pipeline_LoanOfficers.loanofficer
left outer join --NewUnits
(SELECT loanofficer, count(UID) as UnitCount
from pipeline_borrower pb
where Datesigned between @CurrentWeekBegDate and @CurrentWeekEndDate
group by loanofficer) SignedUnits
on SignedUnits.loanofficer = Pipeline_LoanOfficers.loanofficer
left outer join --NewPipeFees
( SELECT loanofficer, sum(brokerfee+processingfee+yieldspread) as TotalFees
from pipeline_borrower pb
where DateSigned BETWEEN @CurrentWeekBegDate and @CurrentWeekEndDate
group by loanofficer) NewPipeFees
on NewPipeFees.loanofficer = Pipeline_LoanOfficers.loanofficer
left outer join --OldPipeUnits
( Select loanofficer, Count(UID) as UnitCount
from pipeline_borrower pb
where ClosingDate BETWEEN @FirstCutBeginDate and @SecondCutEndDate
group by loanofficer) OldPipeUnits
on OldPipeUnits.loanofficer = Pipeline_LoanOfficers.loanofficer
left outer join --OldPipeFees
(Select loanofficer,
sum(brokerfee + processingfee + yieldspread) PipeFeeTotal
from pipeline_borrower
where ClosingDate BETWEEN @FirstCutBeginDate and @SecondCutEndDate
and DateSigned < @CurrentWeekBegDate
group by loanofficer) PipeFees
on PipeFees.loanofficer = Pipeline_LoanOfficers.loanofficer
WHERE Pipeline_LoanOfficers.Role = 'Loan Officer'
ORDER BY 'Closed' DESC

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-05-30 : 15:06:23

quote:
Originally posted by fjstratman

It's an odd application. The Order By 'Closed' sorts the result by most business closed first...

No, it orders by the string value 'Closed', which is the constant for every records so you get no sorting at all.

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources