I am trying to isolate dependencies in a stored procedure I want to bring under test, one of which is a TVF. I have tried to use SpyProcedure on the TVF so I can return a known result but the messages window says:
Cannot use SpyProcedure on dbo.TVF_PROMsGetUploadedScorecardsFilteredByHeaders because the procedure does not exist{Private_ValidateProcedureCanBeUsedWithSpyProcedure,8}
How do I isolate this dependency?
The procedure I would like to test is:
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
ALTER PROCEDURE [dbo].[apPROMSDashboard]
(
@StartDate DATETIME ,
@EndDate DATETIME ,
@AreaID INT ,
@RootReportLevelID INT ,
@PipedHeaderIDs VARCHAR(50) ,
@ProviderID VARCHAR(10) = NULL
)
AS
BEGIN
/*
-- Header Filters set up up-front so they can be used to filter results before not after selecting.
*/
-- This table holds only those IDs that match all the filters.
DECLARE @UploadIDs TABLE
(
PKID INT PRIMARY KEY
IDENTITY(1, 1) ,
UploadedID INT ,
UNIQUE ( UploadedID )
)
INSERT INTO @UploadIDs
SELECT UploadedID
FROM TVF_PROMsGetUploadedScorecardsFilteredByHeaders(@PipedHeaderIDs, NULL)
-- <> -- Get all scorecard results -----------------------
DECLARE @RelevantSummary TABLE
(
PKID INT PRIMARY KEY
IDENTITY ,
FKProcedureID INT ,
PROMSID BIGINT ,
EQ5d_IndexChange DECIMAL(20, 3) ,
EQ5d_ScaleChange DECIMAL(20, 3) ,
EQ5d_ScoreChange DECIMAL(20, 3) ,
UploadID INT ,
UNIQUE ( UploadID )
)
INSERT INTO @RelevantSummary
SELECT DISTINCT
PES.FKProcedureID ,
PES.PROMSID ,
EQ5d_IndexChange ,
EQ5d_ScaleChange ,
EQ5d_ScoreChange ,
FKUploadedScorecardID AS UploadID
FROM tblPROMsExportSummary AS PES
INNER JOIN tvf_GetChildGroups(@AreaID) AS TVF ON TVF.GroupID = PES.FKGroupID
INNER JOIN @UploadIDs AS ULID ON ULID.UploadedID = PES.FKUploadedScorecardID
WHERE PES.ShopDate BETWEEN @StartDate AND @EndDate
AND FKProcedureID IN ( 1, 2, 3, 4 )
-- >< ----------------------------------------------------------------------------------------------
/* Because we use Provider header filter to identify Providers we need to exclude it from ALL results ONLY if running report as a provider */
-- subtract provider id from @PipedHeadersIDs
IF @ProviderID IS NOT NULL
BEGIN
SET @PipedHeaderIDs = REPLACE(@PipedHeaderIDs, @ProviderID, '')
DELETE FROM @UploadIDs
INSERT INTO @UploadIDs
SELECT UploadedID
FROM TVF_PROMsGetUploadedScorecardsFilteredByHeaders(@PipedHeaderIDs, NULL)
END
-- <> -- Get all scorecard results for everything in the root context (SHA) ---------------
DECLARE @RelevantSummaryPCT TABLE
(
PKID INT PRIMARY KEY
IDENTITY ,
FKProcedureID INT ,
PROMSID BIGINT ,
EQ5d_IndexChange DECIMAL(20, 3) ,
EQ5d_ScaleChange DECIMAL(20, 3) ,
EQ5d_ScoreChange DECIMAL(20, 3) ,
UploadID INT
)
INSERT INTO @RelevantSummaryPCT
SELECT DISTINCT
PES.FKProcedureID ,
PES.PROMSID ,
EQ5d_IndexChange ,
EQ5d_ScaleChange ,
EQ5d_ScoreChange ,
FKUploadedScorecardID AS UploadID
FROM tblPROMsExportSummary AS PES
INNER JOIN tvf_GetChildGroups (@RootReportLevelID) AS TVF ON TVF.GroupID = PES.FKGroupID
INNER JOIN @UploadIDs AS ULID ON ULID.UploadedID = PES.FKUploadedScorecardID
WHERE PES.ShopDate BETWEEN @StartDate AND @EndDate
AND FKProcedureID IN ( 1, 2, 3, 4 )
-- >< ----------------------------------------------------------------------------------------------
-- <> -- Return results to GUI, join up user and PCT results ---------------------------------------
SELECT PCT.FKProcedureID ,
ISNULL(IndexChange, -999) AS IndexChange ,
ISNULL(ScaleChange, -999) AS ScaleChange ,
ISNULL(ScoreChange, -999) AS ScoreChange ,
ISNULL(PCTIndex, -999) AS PCTIndex ,
ISNULL(PCTScale, -999) AS PCTScale ,
ISNULL(PCTScore, -999) AS PCTScore
FROM ( SELECT FKProcedureID ,
CAST(AVG(RS.EQ5d_IndexChange) AS DECIMAL(20, 3)) AS IndexChange ,
CAST(AVG(RS.EQ5d_ScaleChange) AS DECIMAL(20, 3)) AS ScaleChange ,
CAST(AVG(RS.EQ5d_ScoreChange) AS DECIMAL(20, 3)) AS ScoreChange
FROM @RelevantSummary AS RS
GROUP BY FKProcedureID ) AS T1
RIGHT JOIN ( SELECT FKProcedureID ,
CAST(AVG(PCT.EQ5d_IndexChange) AS DECIMAL(20, 3)) AS PCTIndex ,
CAST(AVG(PCT.EQ5d_ScaleChange) AS DECIMAL(20, 3)) AS PCTScale ,
CAST(AVG(PCT.EQ5d_ScoreChange) AS DECIMAL(20, 3)) AS PCTScore
FROM @RelevantSummaryPCT AS PCT
GROUP BY FKProcedureID ) AS PCT ON PCT.FKProcedureID = T1.FKProcedureID
ORDER BY PCT.FKProcedureID
END
GO
In my test I fake tblPROMsExportSummary fine, but because the logic in the stored procedure relies on results being returned from the TVF I get no results when I exercise the sproc.
My test:
ALTER PROCEDURE [Dashboard].[test CCG only results]
AS
BEGIN
--Assemble
EXEC tSQLt.FakeTable 'dbo.tblPROMsExportSummary'
EXEC tSQLt.FakeTable 'dbo.tblUploadedScorecardHeaders'
EXEC tsqlt.SpyProcedure @ProcedureName = N'dbo.TVF_PROMsGetUploadedScorecardsFilteredByHeaders', -- nvarchar(max)
@CommandToExecute = N'SELECT 1' -- nvarchar(max)
DECLARE @CCG_ID INT; SET @CCG_ID = 387;
DECLARE @PCT_ID INT; SET @PCT_ID = 374;
DECLARE @StartDate DATETIME; SET @StartDate = '2008-02-01 00:00:00'
--Act
INSERT INTO tblPROMsExportSummary
( PKID ,
PROMSID ,
FKProcedureID ,
EQ5d_IndexChange ,
EQ5d_ScaleChange ,
EQ5d_ScoreChange ,
FKUploadedScorecardID ,
ShopDate ,
FKGroupID )
VALUES ( 1 ,
123456789 , -- PROMSID - bigint
1 , -- FKProcedureID - int
1 , -- EQ5d_IndexChange - decimal
2 , -- EQ5d_ScaleChange - decimal
3 , -- EQ5d_ScoreChange - decimal
187 , -- FKUploadedScorecardID - int
@StartDate , -- ShopDate - datetime
@CCG_ID -- FKGroupID - int
)
INSERT INTO [Dashboard].Actual
EXEC apPROMSDashboard @StartDate, @EndDate = '2012-07-31 23:59:59',
@AreaID = @CCG_ID, @RootReportLevelID = 380, @PipedHeaderIDs = N'', @ProviderID = 0
DECLARE @rows INT;
SET @rows = (SELECT COUNT(*) FROM [Dashboard].Actual)
--Assert
EXEC tSQLt.AssertEquals @Expected = 2, -- sql_variant
@Actual = @rows, -- sql_variant
@Message = N'' -- nvarchar(max)
--EXEC tSQLt.AssertEqualsTable @Expected = N'[Dashboard].Expected', -- nvarchar(max)
-- @Actual = N'[Dashboard].Actual', -- nvarchar(max)
-- @FailMsg = N''
END;
Cannot use SpyProcedure on dbo.TVF_PROMsGetUploadedScorecardsFilteredByHeaders because the procedure does not exist{Private_ValidateProcedureCanBeUsedWithSpyProcedure,8}
How do I isolate this dependency?
The procedure I would like to test is:
In my test I fake tblPROMsExportSummary fine, but because the logic in the stored procedure relies on results being returned from the TVF I get no results when I exercise the sproc.
My test: