Can TVFs be faked

simonjmartin

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;

datacentricity

The closest you can get is faking any underlying tables which should at least reduce some of the setup requirements.

The only other alternative would be to code up an ALTER FUNCTION statement using dynamic SQL that just returns the data you want it to. This would get rolled back along with any other changes when the test completes.

I'm thinking something like this:

DECLARE @sql varchar(MAX);

SET @sql = 'ALTER FUNCTION [dbo].[TVF_PROMsGetUploadedScorecardsFilteredByHeaders]
(
  @PipedHeaderIDs varchar(50)
)
RETURNS @tblVariable table
(
  UploadedID int
)

AS

BEGIN
    INSERT @tblVariable
    (
      UploadedID
    )
          SELECT 99
    UNION SELECT 52
    UNION SELECT 51

    RETURN
END'

BEGIN TRAN

EXEC (@sql)

SELECT * FROM TVF_PROMsGetUploadedScorecardsFilteredByHeaders('')

ROLLBACK TRAN

simonjmartin

Are you saying that if I fake the tables that TVF_PROMsGetUploadedScorecardsFilteredByHeaders references then when it runs it would query the data I've loaded as part of the test. So I could set up those tables to return data that matches what's being expected?

datacentricity

Yes that is exactly what I'm saying. A function (of any type) is just like any other module - if you re-name and re-populate a referenced table (using FakeTable) - the function will use the data you supply from the renamed table(s).

simonjmartin wrote:

Are you saying that if I fake the tables that TVF_PROMsGetUploadedScorecardsFilteredByHeaders references then when it runs it would query the data I've loaded as part of the test. So I could set up those tables to return data that matches what's being expected?