December 16, 2004 at 12:47 pm
I am trying to create a Stored Procedure to pass a variable(FiscalPeriod) into a query to generate a view that calculates Store Count by Fiscal Perod.
this is what I have so far, - what am I doing wrong.
here are the error msgs.
Server: Msg 170, Level 15, State 1, Procedure sp_StoreCt, Line 2
Line 2: Incorrect syntax near 'sp_StoreCt'.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'AS'.
- =============================================
-- Create procedure basic template
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'sp_StoreCt'
AND type = 'P')
DROP PROCEDURE sp_StoreCt
GO
CREATE PROCEDURE sp_StoreCt
DECLARE @CurFYPeriod DateTime
SET @CurFYPeriod = (Select FiscalMonth From FiscalCalendarDaily Where CalendarDate = convert(datetime,convert(char(12), getdate())))
AS
SELECT FCStoreCount.DemandSrcID,
(CASE (FCStoreCount.FYPeriod) WHEN @CurFYPeriod THEN FCStoreCount.StoreCount ELSE 0 END) AS StrCtMthCurr ,
(CASE (FCStoreCount.FYPeriod) WHEN DateAdd( m, - 1, CONVERT(datetime, CONVERT(varchar(10), @CURFYPERIOD, 102), 102))THEN FCStoreCount.StoreCount ELSE 0 END) AS StrCtMth1 ,
(CASE (FCStoreCount.FYPeriod) WHEN DateAdd( m, -2, CONVERT(datetime, CONVERT(varchar(10), @CURFYPERIOD, 102), 102)) THEN FCStoreCount.StoreCount ELSE 0 END) AS StrCtMth2 ,
(CASE (FCStoreCount.FYPeriod) WHEN DateAdd( m, -3, CONVERT(datetime, CONVERT(varchar(10), @CURFYPERIOD, 102), 102)) THEN FCStoreCount.StoreCount ELSE 0 END) AS StrCtMth3 ,
(CASE (FCStoreCount.FYPeriod) WHEN DateAdd( m, -4, CONVERT(datetime, CONVERT(varchar(10), @CURFYPERIOD, 102), 102)) THEN FCStoreCount.StoreCount ELSE 0 END) AS StrCtMth4 ,
(CASE (FCStoreCount.FYPeriod) WHEN DateAdd( m, -5, CONVERT(datetime, CONVERT(varchar(10), @CURFYPERIOD, 102), 102)) THEN FCStoreCount.StoreCount ELSE 0 END) AS StrCtMth5 ,
(CASE (FCStoreCount.FYPeriod) WHEN DateAdd( m, -6, CONVERT(datetime, CONVERT(varchar(10), @CURFYPERIOD, 102), 102)) THEN FCStoreCount.StoreCount ELSE 0 END) AS StrCtMth6 ,
(CASE (FCStoreCount.FYPeriod) WHEN DateAdd( m, -7, CONVERT(datetime, CONVERT(varchar(10), @CURFYPERIOD, 102), 102)) THEN FCStoreCount.StoreCount ELSE 0 END) AS StrCtMth7 ,
(CASE (FCStoreCount.FYPeriod) WHEN DateAdd( m, -8, CONVERT(datetime, CONVERT(varchar(10), @CURFYPERIOD, 102), 102)) THEN FCStoreCount.StoreCount ELSE 0 END) AS StrCtMth8 ,
(CASE (FCStoreCount.FYPeriod) WHEN DateAdd( m, -9, CONVERT(datetime, CONVERT(varchar(10), @CURFYPERIOD, 102), 102)) THEN FCStoreCount.StoreCount ELSE 0 END) AS StrCtMth9 ,
(CASE (FCStoreCount.FYPeriod) WHEN DateAdd( m, -10, CONVERT(datetime, CONVERT(varchar(10), @CURFYPERIOD, 102), 102)) THEN FCStoreCount.StoreCount ELSE 0 END) AS StrCtMth10 ,
(CASE (FCStoreCount.FYPeriod) WHEN DateAdd( m, -11, CONVERT(datetime, CONVERT(varchar(10), @CURFYPERIOD, 102), 102)) THEN FCStoreCount.StoreCount ELSE 0 END) AS StrCtMth11,
(CASE (FCStoreCount.FYPeriod) WHEN DateAdd( m, -12, CONVERT(datetime, CONVERT(varchar(10), @CURFYPERIOD, 102), 102)) THEN FCStoreCount.StoreCount ELSE 0 END) AS StrCtMth12,
(CASE (FCStoreCount.FYPeriod) WHEN DateAdd( m, -13, CONVERT(datetime, CONVERT(varchar(10), @CURFYPERIOD, 102), 102)) THEN FCStoreCount.StoreCount ELSE 0 END) AS StrCtMth13
From FCStoreCount INNER JOIN FiscalCalendarMonthly ON FCStoreCount.FYPeriod = FiscalCalendarMonthly.ReportingMonth
Order BY FCStoreCount.DemandSrcID
GO
-- =============================================
-- example to execute the store procedure
-- =============================================
EXECUTE sp_StoreCt
GO
Thanks,
Karen
December 16, 2004 at 12:49 pm
At first blush change the following
CREATE PROCEDURE sp_StoreCt
DECLARE @CurFYPeriod DateTime
SET @CurFYPeriod = (Select FiscalMonth From FiscalCalendarDaily Where CalendarDate = convert(datetime,convert(char(12), getdate())))
AS
and move the set command after the AS keyword
CREATE PROCEDURE sp_StoreCt
DECLARE @CurFYPeriod DateTime
AS
SET @CurFYPeriod = (Select FiscalMonth From FiscalCalendarDaily Where CalendarDate = convert(datetime,convert(char(12), getdate())))
But a question I have is why have a parameter that just get's defined in the stored procedure? There would never be a need to pass the parameter since the code is setting the value.
If the phone doesn't ring...It's me.
December 17, 2004 at 3:26 am
Hm, why not make it a little bit more generic and use something like
CREATE PROCEDURE sp_StoreCt
@CurFYPeriod DateTime
AS
...
That way you can avoid to have a stored procedure for each possible financial period. You can simply pass the desired period as an argument when you call the sp.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply