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