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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy