Combining two stored procedures to get one result set.
-
I have got two stored procedures as below:
ALTER PROCEDURE [rept].[systemActivities]
-- Add the parameters for the stored procedure here
@client VARCHAR(50),
@startDate DATETIME,
@endDate DATETIME
AS
BEGIN
SET NOCOUNT ON;
DECLARE @results AS TABLE (
[subId] INT,
[total] INT
)
DECLARE
@SubIDint,
@queryStringvarchar(8000),
@currentDateDATETIME,
@jcStartint,
@jcEndint
TRUNCATE TABLE rept.tblActivityList
DECLARE crSystems CURSOR FOR
SELECT
CAST(SUBSTRING(TABLE_NAME, 7, 5) AS int) AS SystemID -- Returning the substring as a integer of the table name.
FROM DB3.information_schema.tables
WHERE
LEFT(TABLE_NAME,6) = 'tblAct'
AND LEN(TABLE_NAME) = 11 -- Avoids tblAct0, tblAct1
AND TABLE_NAME <> 'tblActError' -- Needs to be excluded
AND @client =
(
SELECT TOP(1)
dbo.tblClient.ClientNumber
FROM DB_admin1.dbo.tblClient
INNER JOIN DB_admin1.dbo.tblSystem
ON DB_admin1.dbo.tblSystem.ClientNumber = DB_admin1.dbo.tblClient.ClientNumber
WHERE SystemNum = CAST(SUBSTRING(TABLE_NAME, 7, 5) AS int)
)
ORDER BY TABLE_NAME --Probably better to process the entries in number order
OPEN crSystems
FETCH NEXT FROM crSystems
INTO @SubID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Checking system ' + CAST(@subID AS varchar(10))
SET @currentDate = @startDate
WHILE @currentDate < @endDate
BEGIN
PRINT @currentDate
-- Calculate JCStartDateTime, JCEndDateTime
SET @jcStart = DB_admin1.dbo.fnc_IsoDateTime_JcDateTime(@currentDate)
SET @jcEnd = DB_admin1.dbo.fnc_IsoDateTime_JcDateTime(DATEADD(DAY, 1, @currentDate))
DECLARE @sql AS varchar(8000)
SET @sql =
'INSERT INTO rept.tblActivityList
([systemNum]
,[dtuStart]
,[dtuEnd]
,[total])
VALUES (
' + CAST(@subID AS varchar(10)) + ',
''' + CONVERT(varchar(10), @currentDate, 20) + ''',
DATEADD(DAY, -1, DATEADD(MONTH, 1, ''' + CONVERT(varchar(10), @currentDate, 20) + ''')),
(
SELECT COUNT (*)
FROM DB3.dbo.tblAct' + CAST(@subID AS varchar(10)) + '
WHERE actTimeDate BETWEEN ' + CAST(@jcStart AS varchar(10)) + ' AND ' + CAST(@jcEnd AS varchar(10)) + '
)
)'
--PRINT @sql
EXEC (@sql)
SET @currentDate = DATEADD(MONTH, 1, @currentDate)
END
FETCH NEXT FROM crSystems
INTO @SubID
END
CLOSE crSystems;
DEALLOCATE crSystems;
-- Group the results.
SELECT [dtuStart]
,COUNT(systemNum) AS total
FROM [DB_admin1].[rept].[tblActivityList]
WHERE total > 10
GROUP BY dtuStart
ORDER BY dtuStart
END
AND
ALTER PROCEDURE [rept].[spSubscribedSystems]
-- Add the parameters for the stored procedure here
@startDate DATETIME,
@endDate DATETIME,
@client INT
AS
BEGIN
SET NOCOUNT ON;
TRUNCATE TABLE rept.tblSubscribedSystems
DECLARE @systemSubcriptions AS TABLE(
SystemNum int,
startDate datetime2(0),
endDate datetime2(0)
)
;WITH cteRenewals AS (
-- Rank the renewals in order by System.
SELECT [UniqueId]
,[dbo].[tblRenewals].[SystemNum]
,[Date]
,[Invoice]
,[Renewed]
,[dbo].[tblClient].[ClientNumber]
,RANK() OVER(PARTITION BY [dbo].[tblRenewals].SystemNum ORDER BY UniqueID ASC) as seq
FROM [DB_admin1].[dbo].[tblRenewals]
INNER JOIN dbo.tblSystem ON dbo.tblRenewals.SystemNum = dbo.tblSystem.SystemNum
INNER JOIN dbo.tblClient ON dbo.tblSystem.ClientNumber = dbo.tblClient.ClientNumber
WHERE [dbo].[tblRenewals].SystemNum >= 10000 AND dbo.tblClient.ClientNumber = 1006--@client
),
cteRenewalsWithEndSubscriptionLength AS (
-- Join back on itself, but join to previous sequence
SELECT
ren1.UniqueId,
ren1.SystemNum,
ren2.[Date] AS startDate,
ren1.[Date] AS endDate,
DATEDIFF(DAY, ren2.[Date], ren1.[Date]) AS subLength,
ren1.Invoice,
ren1.Renewed,
ren1.seq AS seq1,
ren2.seq AS seq2
FROM cteRenewals ren1
INNER JOIN cteRenewals ren2
ON ren1.SystemNum = ren2.SystemNum
AND ren1.seq - 1 = ren2.seq -- Disjoint to find previous record for that system
WHERE ren1.seq > 1
)
INSERT INTO @systemSubcriptions
SELECT SystemNum, startDate, endDate
FROM cteRenewalsWithEndSubscriptionLength
WHERE subLength BETWEEN 0 AND 3000-- Ignoring systems with strange values for subscription length
DECLARE
@currentDate datetime2(0),
@monthEnd datetime2(0),
@monthStart datetime2(0)
SET @currentDate = @startDate
-- Loop around each monthn
WHILE @currentDate < @endDate
BEGIN
SET @monthStart = @currentDate
SET @monthEnd = DATEADD(DAY, -1, DATEADD(MONTH, 1, @currentDate))
-- Insert any subscribed system in that month into the report table
INSERT INTO rept.tblSubscribedSystems
SELECT
SystemNum,
@monthStart AS startDate,
@monthEnd AS endDate
FROM @systemSubcriptions sysSubs
WHERE
sysSubs.startDate <= @monthEnd--DATEADD(DAY, 7, @monthStart)--@monthEnd
AND
sysSubs.endDate >= @monthStart
SET @currentDate = DATEADD(MONTH, 1, @currentDate)
END
-- Summarise by start date, total number of systems.
SELECT
dtuStart,
COUNT(DISTINCT(systemNum)) AS [Total Systems]
FROM rept.tblSubscribedSystems
GROUP BY dtuStart
ORDER BY dtuStart
END
When I run both stored procedures by executing :
DECLARE @return_value int
EXEC @return_value = [rept].[systemActivities]
@client = N'1060',
@startDate = N'2000-01-01',
@endDate = N'2020-01-01'
GO
-- Subscription data:
DECLARE @return_value int
EXEC @return_value = [rept].[spSubscribedSystems]
@client = N'1060',
@startDate = N'2000-01-01',
@endDate = N'2020-01-01'
I get the result set:
What I want is to combine both stored procedures.
So in the new result set it will show:
dtuStart and Total Systems from the [rept].[spSubscribedSystems] with the total from the [rept].[systemActivities]
-
Just an idea, maybe not the perfect solution but I might change both of these routines like so:
systemActivities:
INSERT INTO #RESULTS
(
ROW_SOURCE,
dtuStart,
total
)
SELECT
'systemactivities' ROW_SOURCE,
[dtuStart]
,COUNT(systemNum) AS total
FROM [DB_admin1].[rept].[tblActivityList]
WHERE total > 10
GROUP BY dtuStart
ORDER BY dtuStart
spsubscribedsystems:
INSERT INTO #RESULTS
(
ROW_SOURCE,
dtuStart,
total
)
SELECT
'spsubscribedsystems' ROW_SOURCE
dtuStart,
COUNT(DISTINCT(systemNum)) AS [Total Systems]
FROM rept.tblSubscribedSystems
GROUP BY dtuStart
ORDER BY dtuStart
and then create a third routine to call them both after creating temp table #RESULTS
CREATE PROCEDURE callthetwo
AS
BEGIN
CREATE TABLE #RESULTS
(
ROW_SOURCE VARCHAR(50),
dtuStart VARCHAR(50), -- seems like this is varchar from your dynamic sql snippit
total int
)
EXEC systemActivities
EXEC spsubscribedsystems
-- do whatever with result set in #RESULTS
END
-
You can't do much with the results of a stored procedure, other than insert them into a table. You'd be better rewriting the procs as functions, the results of which you can manipulate as you please by JOINing, UNIONing and so on.
John
-
Hi Guys, I have managed to got it all working. I am inserting values into 2 different tables in the stored procedure. I have simply joined these two tables to get the results I was looking for.
if exists (
select * from tempdb.dbo.sysobjects o
where o.xtype in ('U')
and o.id = object_id(N'tempdb..##temp1')
)
DROP TABLE ##temp1;
if exists (
select * from tempdb.dbo.sysobjects o
where o.xtype in ('U')
and o.id = object_id(N'tempdb..##temp2')
)
DROP TABLE ##temp2;
SELECT [dtuStart]
,COUNT(systemNum) AS [Total Activities]
INTO ##temp1
FROM [DB_admin1].[rept].[tblActivityList]
WHERE total > 10
GROUP BY dtuStart
ORDER BY dtuStart
SELECT dtuStart
,COUNT(DISTINCT (systemNum)) AS [Total Number of Systems]
INTO ##temp2
FROM rept.tblSubscribedSystems
GROUP BY dtuStart
ORDER BY dtuStart
SELECT ##temp2.dtuStart AS [Start Date]
,##temp1.[Total Activities]
,##temp2.[Total Number of Systems]
FROM ##temp1
FULL OUTER JOIN ##temp2 ON ##temp1.dtuStart = ##temp2.dtuStart
ORDER BY [Start Date]
Thank you for your time and suggestions.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply