Can TVFs be faked

Easy unit testing for databases in SQL Server Management Studio

Moderators: JonathanWatts, David Atkinson, Anu Deshpande, Michelle Taylor, David Priddle

Can TVFs be faked

Postby simonjmartin » Tue Aug 14, 2012 12:03 pm

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:

Code: Select all
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:
Code: Select all
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;
simonjmartin
 
Posts: 11
Joined: Thu Oct 13, 2011 9:05 am

Not supported but there is a workaround

Postby datacentricity » Tue Aug 14, 2012 5:19 pm

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
\"Your mind is like a parachute, it works best when open\" Frank Zappa
\"Be wary of strong drink. It can make you shoot at tax collectors…and miss\" Robert Heinlein
blog: http://datacentricity.net
twitter: @datacentricity
datacentricity
 
Posts: 19
Joined: Wed Jul 18, 2012 12:22 pm
Location: London

Postby simonjmartin » Thu Aug 16, 2012 10:08 am

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?
simonjmartin
 
Posts: 11
Joined: Thu Oct 13, 2011 9:05 am

Re:

Postby datacentricity » Thu Aug 16, 2012 3:30 pm

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?
\"Your mind is like a parachute, it works best when open\" Frank Zappa
\"Be wary of strong drink. It can make you shoot at tax collectors…and miss\" Robert Heinlein
blog: http://datacentricity.net
twitter: @datacentricity
datacentricity
 
Posts: 19
Joined: Wed Jul 18, 2012 12:22 pm
Location: London


Return to SQL Test

Who is online

Users browsing this forum: No registered users and 1 guest