tSQLt - Database Unit Testing for SQL Server

Database Unit Testing for SQL Server

  • Home
  • Docs
    • Documents
    • Quick Start
    • Articles
  • Training
  • Downloads
tSQLt » Documents » Isolating Dependencies » FakeFunction

FakeFunction

Syntax

tSQLt.FakeFunction [@FunctionName = ] 'function name'
                 , [@FakeFunctionName = ] 'fake function name'

Arguments

[@FunctionName = ] ‘function name’
The name of an existing function. @FunctionName is NVARCHAR(MAX) with no default. @FunctionName should include the schema name of the function. For example: MySchema.MyFunction
[@FakeFunctionName = ] ‘fake function name’
The name of an existing function that will replace the function defined by @FunctionName during the test. @FakeFunctionName is NVARCHAR(MAX) with no default.

Return Code Values

Returns 0

Error Raised

If the function itself or the fake function does not exist, the follow error is raised: ‘function name’ does not exist

If the function and the fake function are not compatible function types (i.e. they must both be scalar functions or both be table valued functions), the following error is raised: Both parameters must contain the name of either scalar or table valued functions!

If the parameters of the function and fake function are not the same, the following error is raised: Parameters of both functions must match! (This includes the return type for scalar functions.)

Result Sets

None

Overview

Code that calls a function can be difficult to test if that function performs significant logic. We want to isolate the code we are testing from the logic buried in the functions that it calls. To create independent tests, we can replace a called function with a fake function. The fake function will perform much simpler logic that supports the purpose of our test. Often, the fake function will simply return a hard-coded value.

Alternatively, the fake function may ‘validate’ the parameters it receives by returning one value if the parameters match expectations, and another value if the parameters do not match expectations. That way the code that calls the function will have a different result and thus the parameter passed to the function can be tested.

Warnings

Remember that if you are faking a function, you are not testing that function. Your test is trying to test something else: typically, the logic of a view, stored procedure or another function that interacts with the function you are faking.

Examples

Example: Using FakeFunction to avoid executing the logic of a complex function
In this example, we want to test a sales report view, SalesReport. The sales report view will return the EmployeeId, RevenueFromSales (the total amount of new revenue the employee generated) and their Commission. (The commision has to be calculated with a complex algorithm using RevenueFromSales and values read from the EmployeeCompensation table. This computation is done by the ComputeCommision scalar function.)

Since we are testing the SalesReport view, we will fake the ComputeCommission function.

EXEC tSQLt.NewTestClass 'SalesAppTests';
GO

CREATE FUNCTION SalesAppTests.Fake_ComputeCommission (
    @EmployeeId INT, 
    @RevenueFromSales DECIMAL(10,4)
)
RETURNS DECIMAL(10,4)
AS
BEGIN
  RETURN 1234.5678;
END;
GO

CREATE PROCEDURE SalesAppTests.[test SalesReport returns revenue and commission]
AS
BEGIN
-------Assemble
    EXEC tSQLt.FakeFunction 'SalesApp.ComputeCommission', 'SalesAppTests.Fake_ComputeCommission';
    EXEC tSQLt.FakeTable 'SalesApp.Employee';
    EXEC tSQLT.FakeTable 'SalesApp.Sales';

    INSERT INTO SalesApp.Employee (EmployeeId) VALUES (1);
    INSERT INTO SalesApp.Sales (EmployeeId, SaleAmount) VALUES (1, 10.1);
    INSERT INTO SalesApp.Sales (EmployeeId, SaleAmount) VALUES (1, 20.2);

-------Act
    SELECT EmployeeId, RevenueFromSales, Commission
      INTO SalesAppTests.Actual
      FROM SalesApp.SalesReport;

-------Assert
    SELECT TOP(0) *
      INTO SalesAppTests.Expected
      FROM SalesAppTests.Actual;

    INSERT INTO SalesAppTests.Expected (EmployeeId, RevenueFromSales, Commission) 
      VALUES (1, 30.3, 1234.5678);

    EXEC tSQLt.AssertEqualsTable 'SalesAppTests.Expected', 'SalesAppTests.Actual';
END;
GO

See Also

  • ApplyConstraint
  • ApplyTrigger
  • FakeFunction
  • FakeTable
  • RemoveObject
  • RemoveObjectIfExists
  • SpyProcedure

Since

V1.0.5071.16906

Share and Enjoy:
  • FacebookFacebook
  • TwitterTwitter
  • LinkedInLinkedIn
  • RedditReddit
  • StumbleUponStumbleUpon
  • TechnoratiTechnorati
  • PrintPrint

Navigation

  • Full user guide
  • Why you should use the tSQLt framework
  • How to get more out of tSQLt
  • Join the conversation
  • Downloads
  • Articles
  • Release Notes
  • New Logo
  • Training
  • Sponsors & Contributors
  • Why you should unit test SQL Server Code
  • Documents
    • Quick Start
    • tSQLt Tutorial
    • tSQLt Keyboard Shortcuts
    • Test Creation and Execution
      • NewTestClass
      • DropClass
      • RunAll
      • Run
      • RenameClass
    • Assertions
      • AssertNotEquals
      • AssertEmptyTable
      • AssertEquals
      • AssertEqualsString
      • AssertEqualsTable
      • AssertObjectExists
      • AssertResultSetsHaveSameMetaData
      • Fail
      • AssertObjectDoesNotExist
      • AssertEqualsTableSchema
      • AssertLike
    • Expectations
      • ExpectException
      • ExpectNoException
    • Isolating Dependencies
      • ApplyConstraint
      • FakeTable
      • SpyProcedure
      • FakeFunction
      • RemoveObjectIfExists
      • ApplyTrigger
      • RemoveObject

Links

  • tSQLt on GitHub
  • tSQLt Mailing List
  • twitter hashtag (#tsqlt)
  • tSQLt tag on stackoverflow.com
  • SQL Server Community on Slack (#tsqlt)
  • #tSQLtLive on YouTube

Sponsors

sqlity.net
redgate.com

A sqlity.net llc Web Property. | ©2010 - 2020, All Rights Reserved. | Privacy Policy | Terms of Use