Msg 156, Level 15, State 1 (Incorrect syntax near the keyword)

I am getting the following error messages when trying to compile this
sp:
Server: Msg 156, Level 15, State 1, Procedure RecordUnitStatDelta, Line
36
Incorrect syntax near the keyword 'NULL'.
Server: Msg 156, Level 15, State 1, Procedure RecordUnitStatDelta, Line
49
Incorrect syntax near the keyword 'ELSE'.
Server: Msg 156, Level 15, State 1, Procedure RecordUnitStatDelta, Line
53
Incorrect syntax near the keyword 'NULL'.
Server: Msg 156, Level 15, State 1, Procedure RecordUnitStatDelta, Line
66
Incorrect syntax near the keyword 'NULL'.
Server: Msg 156, Level 15, State 1, Procedure RecordUnitStatDelta, Line
82
Incorrect syntax near the keyword 'END'.
CREATE PROCEDURE dbo.RecordUnitStatDelta @.iCharterId int, @.iUnitId int
AS
DECLARE cur_trans_awards CURSOR FOR
SELECT Award_id, Original_Effective_date, Effective_date
FROM iAdv_StagingRegAward
WHERE Unit_id = @.iUnitId
AND Charter_id = @.iCharterId
DECLARE @.Award_Id numeric
DECLARE @.Original_Effective_date datetime, @.Effective_date datetime
DECLARE @.o_year int, @.o_month int, @.o_day int
DECLARE @.n_year int, @.n_month int, @.n_day int
OPEN cur_trans_awards
FETCH cur_trans_awards INTO @.Award_Id, @.Original_Effective_date,
@.Effective_date
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.Original_Effective_date IS NULL
BEGIN
IF @.Effective_date IS NULL
-- do nothing; award never earned
-- is_earned = N; is_new = N; is_updated = N
NULL
ELSE
-- insert new award
-- is_new = Y
BEGIN
@.n_year = YEAR(@.Effective_date)
@.n_month = MONTH(@.Effective_date)
INSERT INTO iAdv_UNIT_STAT_DELTA
(unit_id, [year], [month], quantity, rank_code,
award_id)
VALUES
(@.iUnitId, @.n_year, @.n_month, 1, NULL, @.Award_Id)
END
END
ELSE
BEGIN
IF @.Effective_date IS NULL
-- error, cannot change an earned award
NULL
ELSE
@.n_year = YEAR(@.Effective_date)
@.n_month = MONTH(@.Effective_date)
@.n_day = DAY(@.Effective_date)
@.o_year = YEAR(@.Original_Effective_date)
@.o_month = MONTH(@.Original_Effective_date)
@.o_day = DAY(@.Original_Effective_date)
IF @.o_year = @.n_year AND @.o_month = @.n_month AND @.o_day =
@.n_day
-- do nothing; award earned, retained and changed to
same original date
-- is_earned = Y
NULL
ELSE
-- update previously earned award
-- is_updated = N
BEGIN
INSERT INTO iAdv_UNIT_STAT_DELTA
(unit_id, [year], [month], quantity, rank_code,
award_id)
VALUES
(@.iUnitId, @.n_year, @.n_month, 1, NULL, @.Award_Id)
INSERT INTO iAdv_UNIT_STAT_DELTA
(unit_id, [year], [month], quantity, rank_code,
award_id)
VALUES
(@.iUnitId, @.o_year, @.o_month, -1, NULL, @.Award_Id)
END
END
END
CLOSE cur_trans_awards
DEALLOCATE cur_trans_awards
GO
I have tried several different things to get around these syntax errors
with no such luck. Instead of relaying to you all my failed attempts,
I have decided to acknowledge the truth of my being a novice with SQL
Server and s help."rshawtx" <rshawtx@.gmail.com> wrote in message
news:1132094692.961331.297050@.f14g2000cwb.googlegroups.com...
>I am getting the following error messages when trying to compile this
> sp:
>
. . .
> I have tried several different things to get around these syntax errors
> with no such luck. Instead of relaying to you all my failed attempts,
> I have decided to acknowledge the truth of my being a novice with SQL
> Server and s help.
>
Well you might as well start out down the right path. Try to replace
sequential, procedural cursor-based logic with set-based logic. It is
simpler and performs much better:
CREATE PROCEDURE dbo.RecordUnitStatDelta @.iCharterId int, @.iUnitId int
AS
BEGIN
-- insert new awards
INSERT INTO iAdv_UNIT_STAT_DELTA
(unit_id, [year], [month], quantity, rank_code,award_id)
SELECT Unit_id,
YEAR(Effective_date),
MONTH(Effective_date),
1,
NULL,
Award_id
FROM iAdv_StagingRegAward
WHERE Unit_id = @.iUnitId
AND Charter_id = @.iCharterID
AND Original_Effective_date IS NULL
AND EffictiveDate IS NOT NULL
-- update previously earned award
INSERT INTO iAdv_UNIT_STAT_DELTA
(unit_id, [year], [month], quantity, rank_code,award_id)
SELECT Unit_id,
YEAR(Effective_date),
MONTH(Effective_date),
1,
NULL,
Award_id
FROM iAdv_StagingRegAward
WHERE Unit_id = @.iUnitId
AND Charter_id = @.iCharterID
AND Original_Effective_date IS NOT NULL
AND EffictiveDate IS NOT NULL
AND datediff(d,Effective_date,Original_Effec
tive_date) <> 0
INSERT INTO iAdv_UNIT_STAT_DELTA
(unit_id, [year], [month], quantity, rank_code,award_id)
SELECT Unit_id,
YEAR(Original_Effective_date),
MONTH(Original_Effective_date),
-1,
NULL,
Award_id
FROM iAdv_StagingRegAward
WHERE Unit_id = @.iUnitId
AND Charter_id = @.iCharterID
AND Original_Effective_date IS NOT NULL
AND EffictiveDate IS NOT NULL
AND datediff(d,Effective_date,Original_Effec
tive_date) <> 0
END
go
David|||That is indeed a totally different approach from what I am used to.
Best of all, it compiles and works. Thanks David.