In an earlier post in this series, I introduced tSQLt‘s [SpyProcedure] in a test to prove that one procedure calls another. In this article we delve deeper into mocking stored procedures and explore how to populate output parameters or add a row to a table without any of the intervening complex logic in the procedure being mocked.
[tSQLt.SpyProcedure] allows you to record that a call is made to a stored procedure and also what values were passed to that procedure without ever calling the real procedure. You can also define additional logic that will be completed when the mock procedure is called.
For example, imagine you have a procedure, [uspDoTheFirstThing] that contains a series of complex steps that ultimately result in a single row being inserted into a table called [Final]. Those steps depend on data in half a dozen other tables and you have already written tests to confirm that this procedure does everything it should. Now you want to write a new procedure [uspAndAnotherThing] that, among other things, makes a call to [uspDoTheFirstThing] then has some other logic that builds on the row added to [Final]. Without the ability to mock [uspDoTheFirstThing], the only way to write tests for [uspAndAnotherThing] is to also pre-populate the tables used by [uspDoTheFirstThing] in addition to any setup required for the [uspAndAnotherThing] tests. That gets tedious very quickly. [SpyProcedure] allows you to capture the inputs and define the outcomes of [uspDoTheFirstThing] without all the extra set up..
This feature is well documented in the Official tSQLt User Guide but judging by the some of the questions I see cropping up in forums and on internet searches, I don’t think it is so well understood.
Syntax
tSQLt.SpyProcedure [@ProcedureName = ] ‘procedure name’
[, [@CommandToExecute = ] ‘command’ ]Arguments
[@ProcedureName = ] ‘procedure name’ The name of an existing stored procedure. @ProcedureName is NVARCHAR(MAX) with no default. @ProcedureName should include the schema name of the stored procedure. For example: MySchema.MyProcedure
[@CommandToExecute = ] ‘command’ An optional command to execute when a call to Procedure Name is made. @CommandToExecute is NVARCHAR(MAX) with no default.
Let’s start by detailing the objects of interest for this post. As “copy & paste” from web pages doesn’t always work the way it should, you can download all the code samples for this post here.
The [Final] table gets populated by [uspDoTheFirstThing] using some complex but imaginary logic and the data from half a dozen or so tables. This table is then used by [uspAndAnotherThing] later in our process.
[SQL]CREATE TABLE [dbo].[Final]
(
[Id] int NOT NULL IDENTITY(1,1)
, [ExtractCount] int NOT NULL
, [ValueCalculatedFromSixTables] decimal(28,6) NOT NULL
, [ProcessCount] int NULL
, CONSTRAINT PK_Final PRIMARY KEY CLUSTERED ([Id])
);[/SQL]
[uspDoTheFirstThing] is just a stub for the purposes of this post. Imagine that this actually looks up data from a number of other tables and then performs a number of complex logical operations before inserting a row into the [Final] table. We will assume for the purposes of this post that we already have a set of passing unit tests for this procedure.
[SQL]CREATE PROCEDURE dbo.uspDoTheFirstThing
(
@SourceSystem varchar ( 16 )
, @ProcessDate datetime
, @BatchId int = NULL OUT
)
AS
BEGIN
–!
–! Implementation ommitted (stub for unit testing demo purposes)
–!
INSERT dbo.Final
(
ExtractCount
, ValueCalculatedFromSixTables
)
VALUES
(
101
, 1.6377
)
SET @BatchId = SCOPE_IDENTITY();
RETURN(0);
END[/SQL]
[uspAndAnotherThing] is the procedure we are now going to write some tests for. Aside from validating inputs, this procedure calls [uspDoTheFirstThing] which supplies a Batch ID. This batch ID is then used throughout the subsequent processing (most of which has been omitted to help you stay awake).
[SQL]CREATE PROCEDURE dbo.uspAndAnotherThing
(
@SourceSystem varchar ( 16 )
, @ProcessDate datetime
)
AS
BEGIN
SET NOCOUNT ON
–! Standard/ExceptionHandler variables
DECLARE @_Error int
, @_Step nvarchar ( 128 )
, @_Message nvarchar ( 512 )
, @_ErrorContext nvarchar ( 256 )
SET @_Error = 0;
–! Working variables
DECLARE @BatchId int, @ReturnValue int;
BEGIN TRY
SET @_Step = N’Validate inputs’;
IF @SourceSystem IS NULL
BEGIN
RAISERROR(‘Found invalid @SourceSystem: %s’, 16, 1, @SourceSystem);
END
IF @ProcessDate IS NULL OR (CONVERT(char(8), @ProcessDate, 112) > CONVERT(char(8), DATEADD(DAY, -1, GETDATE()), 112))
BEGIN
SET @_Message = ‘Found invalid @ProcessDate: ‘ + COALESCE(CONVERT(char(11), @ProcessDate, 113), ‘NULL’);
RAISERROR(@_Message, 16, 2);
END
SET @_Step = N’Get Batch Id’;
EXEC dbo.uspDoTheFirstThing @SourceSystem, @ProcessDate, @BatchId OUT
–!
–! TODO: Add more code
–!
END TRY
BEGIN CATCH
SET @_ErrorContext = ‘Failed to process trades at step: ‘ + COALESCE(‘[‘ + @_Step + ‘]’, ‘NULL’)
+ ‘ for Source System: ‘ + COALESCE(@SourceSystem, ‘NULL’)
+ ‘ and Process Date: ‘ + COALESCE(CONVERT(char(11), @ProcessDate, 113), ‘NULL’)
–! Rollback any uncommitable transaction
IF XACT_STATE() = -1
BEGIN
ROLLBACK TRAN;
SET @_ErrorContext = @_ErrorContext + ‘ (Rolled back all changes)’;
END
EXEC log4.ExceptionHandler
@ErrorContext = @_ErrorContext
, @ErrorNumber = @_Error OUT
, @ReturnMessage = @_Message OUT
END CATCH
IF @_Error > 0 RAISERROR(@_Message, 16, 99);
SET NOCOUNT OFF;
RETURN (@_Error);
END[/SQL]
One of the first tests we will write is to prove that [uspAndAnotherThing] does actually call [uspDoTheFirstThing] with the correct parameters. For this, we will use tSQLt.SpyProcedure as in the below example.
[SQL]CREATE PROCEDURE [BlogDemoTests].[Test_AndAnotherThing_calls_DoTheFirstThing]
AS
BEGIN
–!
–! Assemble
–!
DECLARE @SourceSystem varchar(16); SET @SourceSystem = ‘XYZ’;
DECLARE @ProcessDate datetime; SET @ProcessDate = ‘20020202’;
–! Mock all the tables affected by this test
EXEC tSQLt.FakeTable @TableName = N’dbo.Final’;
–!
–! Replace the underlying stored procedure with a mock
–! (This will just record the number of executions and inputs
–! in a table called uspDoTheFirstThing_SpyProcedureLog)
–!
EXEC tSQLt.SpyProcedure @ProcedureName = N’dbo.uspDoTheFirstThing’;
–!
–! Use temp tables for the expected and actual values as we don’t
–! then have to explicitly test for the number of times the sproc
–! is called
–!
CREATE TABLE #expected (SourceSystem varchar(16) NULL, ProcessDate datetime NULL, BatchId int NULL)
CREATE TABLE #actual (SourceSystem varchar(16) NULL, ProcessDate datetime NULL, BatchId int NULL)
INSERT #expected VALUES(@SourceSystem, @ProcessDate, NULL);
–!
–! Act
–!
EXEC dbo.uspAndAnotherThing @SourceSystem, @ProcessDate;
INSERT #actual SELECT SourceSystem, ProcessDate, BatchId FROM uspDoTheFirstThing_SpyProcedureLog;
–!
–! Assert
–!
EXEC tSQLt.AssertEqualsTable #expected, #actual
END[/SQL]
This test passes so we can move on to the next piece of logic. We said earlier that [uspAndAnotherThing] needs the @BatchId output from [uspDoTheFirstThing] for all its subsequent processing. And although we should already have tested that [uspDoTheFirstThing] does output a valid Batch ID, because this is such a critical value, I want to ensure that if something unforseen happens and the Batch ID is not valid, [uspAndAnotherThing] will throw an exception. So let’s add that check now…
[SQL]BEGIN TRY
SET @_Step = N’Validate inputs’;
IF @SourceSystem IS NULL
BEGIN
RAISERROR(‘Found invalid @SourceSystem: %s’, 16, 1, @SourceSystem);
END
IF @ProcessDate IS NULL OR (CONVERT(char(8), @ProcessDate, 112) > CONVERT(char(8), DATEADD(DAY, -1, GETDATE()), 112))
BEGIN
SET @_Message = ‘Found invalid @ProcessDate: ‘ + COALESCE(CONVERT(char(11), @ProcessDate, 113), ‘NULL’);
RAISERROR(@_Message, 16, 2);
END
SET @_Step = N’Get Batch Id’;
EXEC @ReturnValue = dbo.uspDoTheFirstThing @SourceSystem, @ProcessDate, @BatchId OUT
–! Check that the Batch ID is valid
IF ISNULL(@BatchId, -1) !> 0
RAISERROR(‘Found invalid batch ID: %d’, 16, 1, @BatchId);
–!
–! TODO: Add more code
–!
END TRY[/SQL]
Adding this additional validation will cause [BlogDemoTests].[Test_AndAnotherThing_calls_DoTheFirstThing] to error like this:
Notice that this is actually an error, not a failure. The test itself passed but the extra validation on @BatchId in [uspAndAnotherThing] threw an exception because Batch ID was output (by the fake version of [uspDoTheFirstThing ]) as NULL
So we need to ensure that the mock version of [uspDoTheFirstThing] created by [SpyProcedure] behaves as it would if it were real. Remember, we’ve isolated any table dependencies with FakeTable so the Batch Id can be any valid number. To do this we just need to change our SpyProcedure call in [Test_AndAnotherThing_calls_DoTheFirstThing] like this:
[SQL]EXEC tSQLt.SpyProcedure @ProcedureName = N’dbo.uspDoTheFirstThing’, @CommandToExecute = N’SET @BatchId = 1′;[/SQL]
Now when we run the same test, the additional validation of @BatchId output value we added to [uspAndAnotherThing] succeeds and the test passes.
So what is actually happening under the hood within the mock version of [uspAndAnotherThing] created by SpyProcedure? Well, after inserting a row into uspDoTheFirstThing_SpyProcedureLog with the input parameter values, it runs whatever SQL you specify in @CommandToExecute.
The ability to have mock procedures do additional SQL tasks is actually really powerful, although .Net developers will take this ability to control mock objects for granted. For the purposes of this post, we are going to skip whatever the implementation is for this procedure and jump to the final step, where the [Final] table gets updated. And, because we like to write robust code, we check that exactly one row was updated – otherwise an error will be thrown. The code in [uspAndAnotherThing] now looks like this:
[SQL]BEGIN TRY
SET @_Step = N’Validate inputs’;
IF @SourceSystem IS NULL
BEGIN
RAISERROR(‘Found invalid @SourceSystem: %s’, 16, 1, @SourceSystem);
END
IF @ProcessDate IS NULL OR (CONVERT(char(8), @ProcessDate, 112) > CONVERT(char(8), DATEADD(DAY, -1, GETDATE()), 112))
BEGIN
SET @_Message = ‘Found invalid @ProcessDate: ‘ + COALESCE(CONVERT(char(11), @ProcessDate, 113), ‘NULL’);
RAISERROR(@_Message, 16, 2);
END
SET @_Step = N’Get Batch Id’;
EXEC @ReturnValue = dbo.uspDoTheFirstThing @SourceSystem, @ProcessDate, @BatchId OUT
–! Check that the Batch ID is valid
IF ISNULL(@BatchId, -1) !> 0
RAISERROR(‘Found invalid batch ID: %d’, 16, 1, @BatchId);
–!
–! Trade processing logic omitted
–!
SET @_Step = N’Update [Final] Table with number of rows processed’;
UPDATE dbo.Final SET ProcessCount = 66 WHERE Id = @BatchId;
SET @_RowCount = @@ROWCOUNT;
IF @_RowCount != 1
RAISERROR(‘Expected @@ROWCOUNT of 1 but found %d’, 16, 1, @_RowCount);
END TRY[/SQL]
When we re-run the original test, we again hit an error like this:
This means we need to make a further modification to the SpyProcedure call in [Test_AndAnotherThing_calls_DoTheFirstThing]. This time, we need to have the mock version of [uspDoTheFirstThing] add a row to the faked [Final] table before setting the output parameter to the same value as the [Id] column (since that is what the UPDATE in [uspAndAnotherThing] is based on). This is what the call to [SpyProcedure] now looks like:
[SQL]DECLARE @command nvarchar(2000);
SET @command = ‘INSERT dbo.Final ([Id], [ExtractCount], [ValueCalculatedFromSixTables], [ProcessCount])’
+ ‘ VALUES (12, 542, 17, NULL);’
+ ‘ SET @BatchId = 12;’
EXEC tSQLt.SpyProcedure @ProcedureName = N’dbo.uspDoTheFirstThing’, @CommandToExecute = @command;[/SQL]
You can see that we are not actually testing any additional functionality with these progressive changes. The test we have been writing only asserts that [uspDoTheFirstThing] gets called exactly once and is passed the correct parameters. The sequence of changes we have made as part of this post replicate the way we might have to refactor [Test_AndAnotherThing_calls_DoTheFirstThing] so that the mock version of [uspDoTheFirstThing] meets the minimum behaviours to allow us to write the rest of our tests. When testing [uspAndAnotherThing], we don’t actually care about the implementation of [uspDoTheFirstThing], all we are interested in is recording the inputs and controlling the outputs. This is the essence of mock objects.
In this post we looked at [SpyProcedure] in more detail and refactored one test to allow us better control over the outputs of a mocked procedure without having to implement any complicated, intervening logic. The next couple of posts in this series will focus on writing tests against views.
