Need help w/ Stored Procedure

[walterja]

What is wrong with this stored procedure? Please help!


CREATE PROCEDURE dbo.pGetMonthClosed
AS
Declare @MonthClosed int

set @MonthClosed = select sum(convert(int, OctClosed) + convert(int, NovClosed) +
convert(int, DecClosed) + convert(int, JanClosed) + convert(int, FebClosed) + convert(int, MarClosed) +
convert(int, AprClosed) + convert(int, MayClosed) + convert(int, JunClosed) + convert(int, JulClosed) +
convert(int, AugClosed) + convert(int, SepClosed))
from tFiscalYear where EndYear = '2007'

Return @MonthClosed
GO

 

[gmmastros]

Your set and select are wrong. It should be like this...

[COLOR=blue]CREATE[/color] [COLOR=blue]PROCEDURE[/color] dbo.pGetMonthClosed
[COLOR=blue]AS[/color]
[COLOR=blue]Declare[/color] @MonthClosed [COLOR=blue]int[/color]

[COLOR=blue]Select[/color] @MonthClosed = sum([COLOR=#FF00FF]convert[/color]([COLOR=blue]int[/color], OctClosed) + [COLOR=#FF00FF]convert[/color]([COLOR=blue]int[/color], NovClosed) + 
[COLOR=#FF00FF]convert[/color]([COLOR=blue]int[/color], DecClosed) + [COLOR=#FF00FF]convert[/color]([COLOR=blue]int[/color], JanClosed) + [COLOR=#FF00FF]convert[/color]([COLOR=blue]int[/color], FebClosed) + [COLOR=#FF00FF]convert[/color]([COLOR=blue]int[/color], MarClosed) +
[COLOR=#FF00FF]convert[/color]([COLOR=blue]int[/color], AprClosed) + [COLOR=#FF00FF]convert[/color]([COLOR=blue]int[/color], MayClosed) + [COLOR=#FF00FF]convert[/color]([COLOR=blue]int[/color], JunClosed) + [COLOR=#FF00FF]convert[/color]([COLOR=blue]int[/color], JulClosed) + 
[COLOR=#FF00FF]convert[/color]([COLOR=blue]int[/color], AugClosed) + [COLOR=#FF00FF]convert[/color]([COLOR=blue]int[/color], SepClosed))
[COLOR=blue]from[/color] tFiscalYear [COLOR=blue]where[/color] EndYear = [COLOR=red]'2007'[/color]

[COLOR=blue]Return[/color] @MonthClosed
[COLOR=blue]GO[/color]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[walterja]

Thanks works great now

 

[ESquared]

gmmastros, there's nothing inherently wrong with the syntax he was using, he just needed parentheses:

set @MonthClosed = [red]([/red]select sum(convert(int, OctClosed) + convert(int, NovClosed) +
convert(int, DecClosed) + convert(int, JanClosed) + convert(int, FebClosed) + convert(int, MarClosed) +
convert(int, AprClosed) + convert(int, MayClosed) + convert(int, JunClosed) + convert(int, JulClosed) +
convert(int, AugClosed) + convert(int, SepClosed))
from tFiscalYear where EndYear = '2007'[red])[/red]

In the case of this particular stored procedure, there is no difference in output.

But there is a difference in what is going on: with the first method, @MonthClosed is not touched in any way if the SELECT returns no rows. With the second method, @MonthClosed is set to NULL because the subquery evaluates to NULL even though no rows were selected.

There may be times when one wants one or the other behavior. It's nice at times with the first method to be able to set a default value in @MonthClosed before running the select (you can toss an IsNull around the subquery, though, too). It's nice at times with the second method to get a definite NULL value if @MonthClosed already has a value in the SP before the query runs.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]