December 21, 2004 at 10:23 am
I have created the following stored procedure with the help of other experts from this website. - I am a newbie,
I need to be able to call this procedure and use its results in a new query to combine with other queries for calculated results. I am unsure what is the best approach to split this procedure into a procedure that calls the Fiscal period and then reference the stored procedures results in a view that completes the task - If this is the case how do I accomplish this and How do I pass the sp results into a select statement? Or is DTS my best approach?
Any assistance is greatly appreciated.
Karen
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE sp_StoreCt
AS
DECLARE @CurFYPeriod DateTime
DECLARE @FYPeriodStart DateTime
Select @CurFYPeriod=FiscalMonth
From FiscalCalendarDaily
Where CalendarDate = convert(datetime,convert(char(12), getdate()))
Select @fyperiodStart=dateadd(m,-13,@curFYperiod)
SELECT ST.DemandSrcID,
Cast(Sum(CASE diff WHEN 0 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMthCurr ,
Cast(Sum(CASE diff WHEN -1 THEN ST.StoreCount ELSE 0 END) as int) AS PrevStrCtMth1 ,
Cast(Sum(CASE diff WHEN -2 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMth2 ,
Cast(Sum(CASE diff WHEN -3 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMth3 ,
Cast(Sum(CASE diff WHEN -4 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMth4 ,
Cast(Sum(CASE diff WHEN -5 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMth5 ,
Cast(Sum(CASE diff WHEN -6 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMth6 ,
Cast(Sum(CASE diff WHEN -7 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMth7 ,
Cast(Sum(CASE diff WHEN -8 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMth8 ,
Cast(Sum(CASE diff WHEN -9 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMth9 ,
Cast(Sum(CASE diff WHEN -10 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMth10 ,
Cast(Sum(CASE diff WHEN -11 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMth11 ,
Cast(Sum(CASE diff WHEN -12 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMth ,
Cast(Sum(CASE diff WHEN -13 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMth13,
Cast(Sum(CASE diff WHEN +1 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMthFC1 ,
Cast(Sum(CASE diff WHEN +2 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMthFC2,
Cast(Sum(CASE diff WHEN +3 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMthFC3,
Cast(Sum(CASE diff WHEN +4 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMthFC4 ,
Cast(Sum(CASE diff WHEN +5 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMthFC5 ,
Cast(Sum(CASE diff WHEN +6 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMthFC6,
Cast(Sum(CASE diff WHEN +7 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMthFC7 ,
Cast(Sum(CASE diff WHEN +8 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMthFC8 ,
Cast(Sum(CASE diff WHEN +9 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMthFC9 ,
Cast(Sum(CASE diff WHEN +10 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMthFC10 ,
Cast(Sum(CASE diff WHEN +11 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMthFC11 ,
Cast(Sum(CASE diff WHEN +12 THEN ST.StoreCount ELSE 0 END) as int) AS StrCtMthFC12
From (select DemandSrcID,Datediff(m,@curfyperiod,fyperiod) as Diff,storecount,fyperiod
from FCStoreCount
Where Fyperiod >= @fyperiodstart
) as ST
INNER JOIN FiscalCalendarMonthly as Cal
ON FYPeriod = Cal.ReportingMonth
group by demandSrcID
Order BY DemandSrcID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
December 21, 2004 at 11:59 am
You can store the results of a stored procedure in a table. Here is an example from one of my procs:
insert into temp_who exec sp_who
You have to have the table defined ahead of time. Once the data is in a table, you can use the table in a view or in any other way that you would use a table.
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
December 23, 2004 at 2:58 am
You could easily implement your SP as a view, by joining your two selects into a single statement.
This would make it easier to join the results from this process with other data.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
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