Red Gate forums :: View topic - Can TVFs be faked
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Test
SQL Test forum

Can TVFs be faked

Search in SQL Test forum
Post new topic   Reply to topic
Jump to:  
Author Message
simonjmartin



Joined: 13 Oct 2011
Posts: 11

PostPosted: Tue Aug 14, 2012 12:03 pm    Post subject: Can TVFs be faked Reply with quote

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:
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:
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;
Back to top
View user's profile Send private message
datacentricity



Joined: 18 Jul 2012
Posts: 18
Location: London

PostPosted: Tue Aug 14, 2012 5:19 pm    Post subject: Not supported but there is a workaround Reply with quote

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
Back to top
View user's profile Send private message
simonjmartin



Joined: 13 Oct 2011
Posts: 11

PostPosted: Thu Aug 16, 2012 10:08 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
datacentricity



Joined: 18 Jul 2012
Posts: 18
Location: London

PostPosted: Thu Aug 16, 2012 3:30 pm    Post subject: Re: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group