Need help finding problem with SP

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)

Need help finding problem with SP

Author

Topic

uxphreak
Starting Member

38 Posts

Posted - 2005-04-12 : 12:35:03

Good morning,

I cannot for the life of me find the syntax problems with my Stored Procedure. When I try to Alter my SP, QA displays the following:

Server: Msg 170, Level 15, State 1, Procedure zsp_Get_Last_Three_Months_From_EndDate, Line 85
Line 85: Incorrect syntax near '.'.
Server: Msg 170, Level 15, State 1, Procedure zsp_Get_Last_Three_Months_From_EndDate, Line 112
Line 112: Incorrect syntax near 'a'.
Server: Msg 170, Level 15, State 1, Procedure zsp_Get_Last_Three_Months_From_EndDate, Line 127
Line 127: Incorrect syntax near 'b'.
Server: Msg 170, Level 15, State 1, Procedure zsp_Get_Last_Three_Months_From_EndDate, Line 142
Line 142: Incorrect syntax near 'c'.

I would greatly appreciate it if someone can take a look at my SP and see where I am running into the problems. I've been looking over this code for the last 30 minutes and am getting too frustrated to look anymore (LOL).

Thanks in advance.

D

-- Get the Last Three Months (Fiscal) from the supplied EndDate (@ed)

ALTER PROCEDURE
zsp_Get_Last_Three_Months_From_EndDate
(
@sd nvarchar(20)
, @ed nvarchar(20)
, @LYStartMonthDay1 datetime OUTPUT
, @LYStartMonthLastDay datetime OUTPUT
, @CYLastMonthDay1 datetime OUTPUT
, @CYLastMonthLastDay datetime OUTPUT
, @TwoMonthsFromEndDate datetime OUTPUT
, @MonthName1 varchar(30) OUTPUT
, @FiscMonthName1 varchar(30) OUTPUT
, @TMFEDStartDate datetime OUTPUT
, @TMFEDEndDate datetime OUTPUT
, @OneMonthFromEndDate datetime OUTPUT
, @MonthName2 varchar(30) OUTPUT
, @FiscMonthName2 varchar(30) OUTPUT
, @OMFEDStartDate datetime OUTPUT
, @OMFEDEndDate datetime OUTPUT
, @CurrentMonth datetime OUTPUT
, @MonthName3 varchar(30) OUTPUT
, @FiscMonthName3 varchar(30) OUTPUT
, @CMStartDate datetime OUTPUT
, @CMEndDate datetime OUTPUT
)
AS
BEGIN
DECLARE
@datecode datetime
SET
@LYStartMonthDay1 =
(
select
StartDate
from
znambe_month_periods
where
description = @sd
)
SET
@LYStartMonthLastDay =
(
select
EndDate
from
znambe_month_periods
where
description = @sd
)
SET
@CYLastMonthDay1 =
(
select
StartDate
from
znambe_month_periods
where
description = @ed
)
SET
@CYLastMonthLastDay =
(
select
EndDate
from
znambe_month_periods
where
description = @ed
)
SET
@datecode =
(
SELECT
datecode
FROM
znambe_month_periods
WHERE
@CYLastMonthLastDay = enddate
)
SELECT
@TwoMonthsFromEndDate = a.dc1a
, @MonthName1 = a.desc1b
, @FiscMonthName1 a.desc1a
, @TMFEDStartDate = a.sd1a
, @TMFEDEndDate = a.ed1a
, @OneMonthFromEndDate = b.dc2a
, @MonthName2 = b.desc2b
, @FiscMonthName2 = b.desc2a
, @OMFEDStartDate = b.sd2a
, @OMFEDEndDate = b.ed2a
, @CurrentMonthDC = c.dc3a
, @MonthName3 = c.desc3b
, @FiscMonthName3 = c.desc3a
, @CMStartDate = c.sd3a
, @CMEndDate = c.ed3a
FROM
(
SELECT
description AS desc1a
, CAST(DATENAME(month,datecode) as varchar(30)) as desc1b
, datecode as dc1a
, startdate as sd1a
, enddate as ed1a
FROM
znambe_month_periods
WHERE
dbo.z_uf_MDate(Year(@datecode),Month(@datecode)-2,1) >= startdate
and
dbo.z_uf_MDate(Year(@datecode),Month(@datecode)-2,1) <= enddate
)a
,
(
SELECT
description AS desc2a
, CAST(DATENAME(month,datecode) as varchar(30)) as desc2b
, datecode as dc2a
, startdate as sd2a
, enddate as ed2a
FROM
znambe_month_periods
WHERE
dbo.z_uf_MDate(Year(@datecode),Month(@datecode)-1,1) >= startdate
and
dbo.z_uf_MDate(Year(@datecode),Month(@datecode)-1,1) <= enddate
)b
,
(
SELECT
description AS desc3a
, CAST(DATENAME(month,datecode) as varchar(30)) as desc3b
, datecode as dc3a
, startdate as sd3a
, enddate as ed3a
FROM
znambe_month_periods
WHERE
dbo.z_uf_MDate(Year(@datecode),Month(@datecode),1) >= startdate
and
dbo.z_uf_MDate(Year(@datecode),Month(@datecode),1) <= enddate
)c
END

uxphreak
Starting Member

38 Posts

Posted - 2005-04-12 : 13:15:02

Found the problem. I was missing an '=' in ", @FiscMonthName1 a.desc1a"

Thanks,

D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources