Effective Unit Testing for SQL Server


Gavin Campbell

arapaima.uk

Stay in touch

What's so great about Unit Testing anyway?

What is Unit Testing?

What is a "Unit"?

The primary goal of unit testing is to take the smallest piece of testable software in the application, isolate it from the remainder of the code, and determine whether it behaves exactly as you expect.
MSDN, Visual Studio 2003 Documentation

What makes a "good" Unit Test

  • Fast
  • Independent
  • Reliable
  • Specific

The Test Pyramid

The Test Pyramid

The Test Pyramid

The Test Pyramid

Stephen R Covey, The 7 Habits of Highly Effective People

The Test Pyramid

Stephen R Covey, The 7 Habits of Highly Effective People

The Test Pyramid

Stephen R Covey, The 7 Habits of Highly Effective People

The Test Pyramid

Stephen R Covey, The 7 Habits of Highly Effective People

A simple test

The code under test


CREATE PROCEDURE AddTrackToAlbum 
@TrackId int, @AlbumId int
AS
BEGIN
    UPDATE dbo.Track 
    SET AlbumId = @AlbumId 
    WHERE TrackId = @TrackId
END

                    

A "test script"

 
INSERT INTO dbo.Artist(ArtistId,Name) 
    VALUES (276, 'Showaddywaddy');

INSERT INTO dbo.Album (AlbumId, Title, ArtistId) 
    VALUES(348, 'Trocadero', 276);

INSERT INTO dbo.Track(TrackId, Name, AlbumId, MediaTypeId, 
    Milliseconds, UnitPrice)
    VALUES (3504, 'Under The Moon of Love', 348, 
    2, 180000, 999.99);

EXEC dbo.AddTrackToAlbum @TrackID = 3504, @AlbumId = 348;

SELECT * FROM Album WHERE AlbumId = 348;
AlbumId Title ArtistId
348 Trocadero 276

ARRANGE



INSERT INTO dbo.Artist(ArtistId,Name) 
    VALUES (276, 'Showaddywaddy');

INSERT INTO dbo.Album (AlbumId, Title, ArtistId) 
    VALUES(348, 'Trocadero', 276);

INSERT INTO dbo.Track(TrackId, Name, AlbumId, MediaTypeId, 
    Milliseconds, UnitPrice)
    VALUES (3504, 'Under The Moon of Love', 348, 
    2, 180000, 999.99);

EXEC dbo.AddTrackToAlbum @TrackID = 3504, @AlbumId = 348;

SELECT * FROM Album WHERE AlbumId = 348;

ACT


INSERT INTO dbo.Artist(ArtistId,Name) 
    VALUES (276, 'Showaddywaddy');

INSERT INTO dbo.Album (AlbumId, Title, ArtistId) 
    VALUES(348, 'Trocadero', 276);

INSERT INTO dbo.Track(TrackId, Name, AlbumId, MediaTypeId, 
    Milliseconds, UnitPrice)
    VALUES (3504, 'Under The Moon of Love', 348, 
    2, 180000, 999.99);

EXEC dbo.AddTrackToAlbum @TrackID = 3504, @AlbumId = 348;

SELECT * FROM Album WHERE AlbumId = 348;

"ASSERT"


INSERT INTO dbo.Artist(ArtistId,Name) 
    VALUES (276, 'Showaddywaddy');

INSERT INTO dbo.Album (AlbumId, Title, ArtistId) 
    VALUES(348, 'Trocadero', 276);

INSERT INTO dbo.Track(TrackId, Name, AlbumId, MediaTypeId, 
    Milliseconds, UnitPrice)
    VALUES (3504, 'Under The Moon of Love', 348, 
    2, 180000, 999.99);

EXEC dbo.AddTrackToAlbum @TrackID = 3504, @AlbumId = 348;

SELECT * FROM Album WHERE AlbumId = 348;

"ARRANGE - ACT - ASSERT"


	--Arrange
	INSERT INTO dbo.Artist(ArtistId,Name) 
		VALUES (276, 'Showaddywaddy');
	
	INSERT INTO dbo.Album (AlbumId, Title, ArtistId) 
		VALUES(348, 'Trocadero', 276);
	
	INSERT INTO dbo.Track(TrackId, Name, AlbumId, MediaTypeId, 
		Milliseconds, UnitPrice)
		VALUES (3504, 'Under The Moon of Love', 348, 
		2, 180000, 999.99);
	--Act
	EXEC dbo.AddTrackToAlbum @TrackID = 3504, @AlbumId = 348;
	--Assert					
	SELECT * FROM Album WHERE AlbumId = 348;
	
	

A Library of Tests

Testing Frameworks

Tools for Creating Tests
  • Attributes
  • Assertions

Tools for Running Tests
  • Graphical Test Runners
  • Command Line Test Runners

Tools for Parsing output
  • Human Readable
  • Machine Readable

Test Frameworks Family Tree

tSQLt

tSQLt

Tools for creating tests
  • Test Classes
  • Assertions

Tools for running tests
  • tSQLt.Run
  • tSQLt.RunTestClass

Tools for parsing output
  • Text / XML

Tools for isolating dependencies
  • tSQLt.FakeTable, tSQLt.FakeFunction, etc.
  • tSQLt.SpyProcedure
  • Installation

    
    						/*
    						Copyright 2011 tSQLt
    					 
    						Licensed under the Apache License, Version 2.0 (the "License");
    						you may not use this file except in compliance with the License.
    						You may obtain a copy of the License at
    					 
    							http://www.apache.org/licenses/LICENSE-2.0
    					 
    						Unless required by applicable law or agreed to in writing, software
    						distributed under the License is distributed on an "AS IS" BASIS,
    						WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    						See the License for the specific language governing permissions and
    						limitations under the License.
    					 */
    					 DECLARE @Msg NVARCHAR(MAX);SELECT @Msg = 'Installed at '+CONVERT(NVARCHAR,GETDATE(),121);RAISERROR(@Msg,0,1);
    					 GO
    					 
    					 IF TYPE_ID('tSQLt.Private') IS NOT NULL DROP TYPE tSQLt.Private;
    					 IF TYPE_ID('tSQLtPrivate') IS NOT NULL DROP TYPE tSQLtPrivate;
    					 GO
    					 IF OBJECT_ID('tSQLt.DropClass') IS NOT NULL
    						 EXEC tSQLt.DropClass tSQLt;
    					 GO
    					 
    					 IF EXISTS (SELECT 1 FROM sys.assemblies WHERE name = 'tSQLtCLR')
    						 DROP ASSEMBLY tSQLtCLR;
    					 GO
    					 
    					 CREATE SCHEMA tSQLt;
    					 GO
    					 SET QUOTED_IDENTIFIER ON;
    					 GO
    					 
    					  
    					 ...
    					 ...
    				

    Installation

    
    					/*
    					Copyright 2011 tSQLt
    				 
    					Licensed under the Apache License, Version 2.0 (the "License");
    					you may not use this file except in compliance with the License.
    					You may obtain a copy of the License at
    				 
    						http://www.apache.org/licenses/LICENSE-2.0
    				 
    					Unless required by applicable law or agreed to in writing, software
    					distributed under the License is distributed on an "AS IS" BASIS,
    					WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    					See the License for the specific language governing permissions and
    					limitations under the License.
    				 */
    				 DECLARE @Msg NVARCHAR(MAX);SELECT @Msg = 'Installed at '+CONVERT(NVARCHAR,GETDATE(),121);RAISERROR(@Msg,0,1);
    				 GO
    				 
    				 IF TYPE_ID('tSQLt.Private') IS NOT NULL DROP TYPE tSQLt.Private;
    				 IF TYPE_ID('tSQLtPrivate') IS NOT NULL DROP TYPE tSQLtPrivate;
    				 GO
    				 IF OBJECT_ID('tSQLt.DropClass') IS NOT NULL
    					 EXEC tSQLt.DropClass tSQLt;
    				 GO
    				 
    				 IF EXISTS (SELECT 1 FROM sys.assemblies WHERE name = 'tSQLtCLR')
    					 DROP ASSEMBLY tSQLtCLR;
    				 GO
    				 
    				 CREATE SCHEMA tSQLt;
    				 GO
    				 SET QUOTED_IDENTIFIER ON;
    				 GO
    				 
    				  
    				 ...
    				 ...
    			
    
    					Installed at 2017-11-03 07:40:03.010
    					
    				   +-----------------------------------------+
    				   |                                         |
    				   | Thank you for using tSQLt.              |
    				   |                                         |
    				   | tSQLt Version: 1.0.5873.27393           |
    				   |                                         |
    				   +-----------------------------------------+
    				   
    				
    			

    tSQLt "Rules"

    • A test class is a SQL Server schema with some extended properties, created with the tSQLt.NewTestClass stored procedure.
    • A test case is a stored procedure that belongs to a schema identified as a test class, the name of which starts with the word "test".
    • I can't think of any more rules.

    A tSQLt Test

    
    EXEC tSQLt.NewTestClass @ClassName = N'AddTrackToAlbum'
    GO
    CREATE PROCEDURE 
    AddTrackToAlbum.[test existing track added to existing album succeeds]
    AS
    BEGIN
    
    EXEC tSQLt.FakeTable 'dbo.Album';
    EXEC tSQLt.FakeTable 'dbo.Track';
    DECLARE @track_id int = 345, @album_id int = 456
    INSERT INTO dbo.Album(AlbumId) VALUES (@album_id);
    INSERT INTO dbo.Track(TrackId) VALUES (@track_id);
    
    EXEC dbo.AddTrackToAlbum @TrackId = @track_id, @AlbumId = @album_id;
    
    SELECT @album_id as AlbumId, @track_id as TrackId into Expected;
    SELECT AlbumId, TrackId INTO Actual FROM Track;
    
    EXEC tSQLt.AssertEqualsTable 
    @Expected = N'Expected', @Actual = N'Actual';
    END
    

    Arrange

    
    EXEC tSQLt.NewTestClass @ClassName = N'AddTrackToAlbum'
    GO
    CREATE PROCEDURE 
    AddTrackToAlbum.[test existing track added to existing album succeeds]
    AS
    BEGIN
    
    EXEC tSQLt.FakeTable 'dbo.Album';
    EXEC tSQLt.FakeTable 'dbo.Track';
    DECLARE @track_id int = 345, @album_id int = 456
    INSERT INTO dbo.Album(AlbumId) VALUES (@album_id);
    INSERT INTO dbo.Track(TrackId) VALUES (@track_id);
    
    EXEC dbo.AddTrackToAlbum @TrackId = @track_id, @AlbumId = @album_id;
    
    SELECT @album_id as AlbumId, @track_id as TrackId into Expected;
    SELECT AlbumId, TrackId INTO Actual FROM Track;
    
    EXEC tSQLt.AssertEqualsTable 
    @Expected = N'Expected', @Actual = N'Actual';
    END
    

    Act

    
    EXEC tSQLt.NewTestClass @ClassName = N'AddTrackToAlbum'
    GO
    CREATE PROCEDURE 
    AddTrackToAlbum.[test existing track added to existing album succeeds]
    AS
    BEGIN
    
    EXEC tSQLt.FakeTable 'dbo.Album';
    EXEC tSQLt.FakeTable 'dbo.Track';
    DECLARE @track_id int = 345, @album_id int = 456
    INSERT INTO dbo.Album(AlbumId) VALUES (@album_id);
    INSERT INTO dbo.Track(TrackId) VALUES (@track_id);
    
    EXEC dbo.AddTrackToAlbum @TrackId = @track_id, @AlbumId = @album_id;
    
    SELECT @album_id as AlbumId, @track_id as TrackId into Expected;
    SELECT AlbumId, TrackId INTO Actual FROM Track;
    
    EXEC tSQLt.AssertEqualsTable 
    @Expected = N'Expected', @Actual = N'Actual';
    END
    

    Assert

    
    EXEC tSQLt.NewTestClass @ClassName = N'AddTrackToAlbum'
    GO
    CREATE PROCEDURE 
    AddTrackToAlbum.[test existing track added to existing album succeeds]
    AS
    BEGIN
    
    EXEC tSQLt.FakeTable 'dbo.Album';
    EXEC tSQLt.FakeTable 'dbo.Track';
    DECLARE @track_id int = 345, @album_id int = 456
    INSERT INTO dbo.Album(AlbumId) VALUES (@album_id);
    INSERT INTO dbo.Track(TrackId) VALUES (@track_id);
    
    EXEC dbo.AddTrackToAlbum @TrackId = @track_id, @AlbumId = @album_id;
    
    SELECT @album_id as AlbumId, @track_id as TrackId into Expected;
    SELECT AlbumId, TrackId INTO Actual FROM Track;
    
    EXEC tSQLt.AssertEqualsTable 
    @Expected = N'Expected', @Actual = N'Actual';
    END
    

    A tSQLt Test

    
    EXEC tSQLt.NewTestClass @ClassName = N'AddTrackToAlbum'
    GO
    CREATE PROCEDURE 
    AddTrackToAlbum.[test existing track added to existing album succeeds]
    AS
    BEGIN
    
    EXEC tSQLt.FakeTable 'dbo.Album';
    EXEC tSQLt.FakeTable 'dbo.Track';
    DECLARE @track_id int = 345, @album_id int = 456
    INSERT INTO dbo.Album(AlbumId) VALUES (@album_id);
    INSERT INTO dbo.Track(TrackId) VALUES (@track_id);
    
    EXEC dbo.AddTrackToAlbum @TrackId = @track_id, @AlbumId = @album_id;
    
    SELECT @album_id as AlbumId, @track_id as TrackId into Expected;
    SELECT AlbumId, TrackId INTO Actual FROM Track;
    
    EXEC tSQLt.AssertEqualsTable 
    @Expected = N'Expected', @Actual = N'Actual';
    END
    

    A tSQLt Test

    
    EXEC tSQLt.NewTestClass @ClassName = N'AddTrackToAlbum'
    GO
    CREATE PROCEDURE 
    AddTrackToAlbum.[test existing track added to existing album succeeds]
    AS
    BEGIN
    
    EXEC tSQLt.FakeTable 'dbo.Album';
    EXEC tSQLt.FakeTable 'dbo.Track';
    DECLARE @track_id int = 345, @album_id int = 456
    INSERT INTO dbo.Album(AlbumId) VALUES (@album_id);
    INSERT INTO dbo.Track(TrackId) VALUES (@track_id);
    
    EXEC dbo.AddTrackToAlbum @TrackId = @track_id, @AlbumId = @album_id;
    
    SELECT @album_id as AlbumId, @track_id as TrackId into Expected;
    SELECT AlbumId, TrackId INTO Actual FROM Track;
    
    EXEC tSQLt.AssertEqualsTable 
    @Expected = N'Expected', @Actual = N'Actual';
    END
    

    A tSQLt Test

    
    EXEC tSQLt.NewTestClass @ClassName = N'AddTrackToAlbum'
    GO
    CREATE PROCEDURE 
    AddTrackToAlbum.[test existing track added to existing album succeeds]
    AS
    BEGIN
    		
    EXEC tSQLt.FakeTable 'dbo.Album';
    EXEC tSQLt.FakeTable 'dbo.Track';
    DECLARE @track_id int = 345, @album_id int = 456
    INSERT INTO dbo.Album(AlbumId) VALUES (@album_id);
    INSERT INTO dbo.Track(TrackId) VALUES (@track_id);
    
    EXEC dbo.AddTrackToAlbum @TrackId = @track_id, @AlbumId = @album_id;
    
    SELECT @album_id as AlbumId, @track_id as TrackId into Expected;
    SELECT AlbumId, TrackId INTO Actual FROM Track;
    
    EXEC tSQLt.AssertEqualsTable 
    @Expected = N'Expected', @Actual = N'Actual';
    END
    

    A tSQLt Test

    
    	EXEC tSQLt.NewTestClass @ClassName = N'AddTrackToAlbum'
    	GO
    	CREATE PROCEDURE 
    	AddTrackToAlbum.[test existing track added to existing album succeeds]
    	AS
    	BEGIN
    			
    	EXEC tSQLt.FakeTable 'dbo.Album';
    	EXEC tSQLt.FakeTable 'dbo.Track';
    	DECLARE @track_id int = 345, @album_id int = 456
    	INSERT INTO dbo.Album(AlbumId) VALUES (@album_id);
    	INSERT INTO dbo.Track(TrackId) VALUES (@track_id);
    	
    	EXEC dbo.AddTrackToAlbum @TrackId = @track_id, @AlbumId = @album_id;
    	
    	SELECT @album_id as AlbumId, @track_id as TrackId into Expected;
    	SELECT AlbumId, TrackId INTO Actual FROM Track;
    	
    	EXEC tSQLt.AssertEqualsTable 
    	@Expected = N'Expected', @Actual = N'Actual';
    	END
    	

    A tSQLt Test

    
    		EXEC tSQLt.NewTestClass @ClassName = N'AddTrackToAlbum'
    		GO
    		CREATE PROCEDURE 
    		AddTrackToAlbum.[test existing track added to existing album succeeds]
    		AS
    		BEGIN
    				
    		EXEC tSQLt.FakeTable 'dbo.Album';
    		EXEC tSQLt.FakeTable 'dbo.Track';
    		DECLARE @track_id int = 345, @album_id int = 456
    		INSERT INTO dbo.Album(AlbumId) VALUES (@album_id);
    		INSERT INTO dbo.Track(TrackId) VALUES (@track_id);
    		
    		EXEC dbo.AddTrackToAlbum @TrackId = @track_id, @AlbumId = @album_id;
    		
    		SELECT @album_id as AlbumId, @track_id as TrackId into Expected;
    		SELECT AlbumId, TrackId INTO Actual FROM Track;
    		
    		EXEC tSQLt.AssertEqualsTable 
    		@Expected = N'Expected', @Actual = N'Actual';
    		END
    		

    A tSQLt Test

    
    EXEC tSQLt.NewTestClass @ClassName = N'AddTrackToAlbum'
    GO
    CREATE PROCEDURE 
    AddTrackToAlbum.[test existing track added to existing album succeeds]
    AS
    BEGIN
    
    EXEC tSQLt.FakeTable 'dbo.Album';
    EXEC tSQLt.FakeTable 'dbo.Track';
    DECLARE @track_id int = 345, @album_id int = 456
    INSERT INTO dbo.Album(AlbumId) VALUES (@album_id);
    INSERT INTO dbo.Track(TrackId) VALUES (@track_id);
    
    EXEC dbo.AddTrackToAlbum @TrackId = @track_id, @AlbumId = @album_id;
    
    SELECT @album_id as AlbumId, @track_id as TrackId into Expected;
    SELECT AlbumId, TrackId INTO Actual FROM Track;	
    
    EXEC tSQLt.AssertEqualsTable 
    @Expected = N'Expected', @Actual = N'Actual';
    END
    

    A tSQLt Test

    
    EXEC tSQLt.NewTestClass @ClassName = N'AddTrackToAlbum'
    GO
    CREATE PROCEDURE 
    AddTrackToAlbum.[test existing track added to existing album succeeds]
    AS
    BEGIN
    
    EXEC tSQLt.FakeTable 'dbo.Album';
    EXEC tSQLt.FakeTable 'dbo.Track';
    DECLARE @track_id int = 345, @album_id int = 456
    INSERT INTO dbo.Album(AlbumId) VALUES (@album_id);
    INSERT INTO dbo.Track(TrackId) VALUES (@track_id);
    
    EXEC dbo.AddTrackToAlbum @TrackId = @track_id, @AlbumId = @album_id;
    
    SELECT @album_id as AlbumId, @track_id as TrackId into Expected;
    SELECT AlbumId, TrackId INTO Actual FROM Track;
    
    EXEC tSQLt.AssertEqualsTable 
    @Expected = N'Expected', @Actual = N'Actual';
    END
    

    Running the test

    EXEC tSQLt.RunAll;
    
    +----------------------+
    |Test Execution Summary|
    +----------------------+
    
    |No|Test Case Name                                                            |Dur(ms)|Result |
    +--+--------------------------------------------------------------------------+-------+-------+
    |1 |[AddTrackToAlbum].[test existing track added to existing album succeeds]|     40|Success|
    -----------------------------------------------------------------------------
    Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored.
    -----------------------------------------------------------------------------
    

    Making the test fail

    
    ALTER PROCEDURE [dbo].[AddTrackToAlbum] 
    @TrackId int, @AlbumId int
    AS
    
    UPDATE dbo.Track SET AlbumId = NULL WHERE TrackId = @TrackId
    GO
    
    
    EXEC tSQLt.RunAll;
    
    
    		[AddTrackToAlbum].[test existing track added to existing album succeeds] failed: (Failure) Unexpected/missing resultset rows!
    		|_m_|AlbumId|TrackId|
    		+---+-------+-------+
    		|<  |456    |345    |
    		|>  |!NULL! |345    |
    		 
    		+----------------------+
    		|Test Execution Summary|
    		+----------------------+
    		 
    		|No|Test Case Name                                                            |Dur(ms)|Result |
    		+--+--------------------------------------------------------------------------+-------+-------+
    		|1 |[AddTrackToAlbum].[test existing track added to existing album succeeds]|    360|Failure|
    		-----------------------------------------------------------------------------
    		Msg 50000, Level 16, State 10, Line 30
    		Test Case Summary: 1 test case(s) executed, 0 succeeded, 1 failed, 0 errored.
    		-----------------------------------------------------------------------------
    		

    Making the test fail

    
    						CREATE PROCEDURE AddTrackToAlbum 
    						@TrackId int, @AlbumId int
    						AS
    						BEGIN
    							UPDATE dbo.Track 
    							SET AlbumId = @AlbumId 
    							WHERE TrackId = @TrackId
    						END
    					
    
    
    					CREATE PROCEDURE 
    					AddTrackToAlbum.[test existing track added to existing album succeeds]
    					AS
    					BEGIN
    					
    					EXEC tSQLt.FakeTable 'dbo.Album';
    					EXEC tSQLt.FakeTable 'dbo.Track';
    					DECLARE @track_id int = 345, @album_id int = 456
    					INSERT INTO dbo.Album(AlbumId) VALUES (@album_id);
    					INSERT INTO dbo.Track(TrackId) VALUES (@track_id);
    					
    					EXEC dbo.AddTrackToAlbum @TrackId = @track_id, @AlbumId = @album_id;
    					
    					SELECT @album_id as AlbumId, @track_id as TrackId into Expected;
    					SELECT AlbumId, TrackId INTO Actual FROM Track;
    					
    					EXEC tSQLt.AssertEqualsTable 
    					@Expected = N'Expected', @Actual = N'Actual';
    					END

    Making the test fail

    
    						CREATE PROCEDURE AddTrackToAlbum 
    						@TrackId int, @AlbumId int
    						AS
    						BEGIN
    							UPDATE dbo.Track 
    							SET AlbumId = @AlbumId + 1 
    							WHERE TrackId = @TrackId
    						END
    					
    
    
    					CREATE PROCEDURE 
    					AddTrackToAlbum.[test existing track added to existing album succeeds]
    					AS
    					BEGIN
    					
    					EXEC tSQLt.FakeTable 'dbo.Album';
    					EXEC tSQLt.FakeTable 'dbo.Track';
    					DECLARE @track_id int = 345, @album_id int = 456
    					INSERT INTO dbo.Album(AlbumId) VALUES (@album_id);
    					INSERT INTO dbo.Track(TrackId) VALUES (@track_id);
    					
    					EXEC dbo.AddTrackToAlbum @TrackId = @track_id, @AlbumId = @album_id;
    					
    					SELECT @album_id as AlbumId, @track_id as TrackId into Expected;
    					SELECT AlbumId, TrackId INTO Actual FROM Track;
    					
    					EXEC tSQLt.AssertEqualsTable 
    					@Expected = N'Expected', @Actual = N'Actual';
    					END

    Making the test fail

    
    						CREATE PROCEDURE AddTrackToAlbum 
    						@TrackId int, @AlbumId int
    						AS
    						BEGIN
    							UPDATE dbo.Track 
    							SET AlbumId = @AlbumId + 1
    							WHERE TrackId = @TrackId
    						END
    					
    
    
    					CREATE PROCEDURE 
    					AddTrackToAlbum.[test existing track added to existing album succeeds]
    					AS
    					BEGIN
    					
    					EXEC tSQLt.FakeTable 'dbo.Album';
    					EXEC tSQLt.FakeTable 'dbo.Track';
    					DECLARE @track_id int = 345, @album_id int = 456
    					INSERT INTO dbo.Album(AlbumId) VALUES (@album_id);
    					INSERT INTO dbo.Track(TrackId) VALUES (@track_id);
    					
    					EXEC dbo.AddTrackToAlbum @TrackId = @track_id, @AlbumId = @album_id;
    					
    					SELECT @album_id as AlbumId, NULL as TrackId into Expected;
    					SELECT AlbumId, TrackId INTO Actual FROM Track;
    					
    					EXEC tSQLt.AssertEqualsTable 
    					@Expected = N'Expected', @Actual = N'Actual';
    					END

    When adding tests to legacy code, change the code, not the test, to generate a failure


    Custom Failure Messages

    EXEC tSQLt.AssertEqualsTable 
    @Expected = N'Expected', @Actual = N'Actual',
    @FailMsg = 'Failed to add track to existing Album';
    

    
    [AddTrackToAlbum].[test existing track added to existing album succeeds] failed: 
    (Failure) Failed to add track to existing Album
    |_m_|AlbumId|TrackId|
    +---+-------+-------+
    |<  |456    |345    |
    |>  |!NULL! |345    |    
    
    
    			[AddTrackToAlbum].[test existing track added to existing album succeeds] failed: 
    			(Failure) Failed to add track to existing Album
    			|_m_|AlbumId|TrackId|
    			+---+-------+-------+
    			|<  |456    |345    |
    			|>  |!NULL! |345    |    
    							
    • The thing we are testing
    • What we did
    • What we expected to happen
    
    			[AddTrackToAlbum].[test existing track added to existing album succeeds] failed: 
    			(Failure) Failed to add track to existing Album
    			|_m_|AlbumId|TrackId|
    			+---+-------+-------+
    			|<  |456    |345    |
    			|>  |!NULL! |345    |    
    							
    • The thing we are testing
    • What we did
    • What we expected to happen
    
    			[AddTrackToAlbum].[test existing track added to existing album succeeds] failed: 
    			(Failure) Failed to add track to existing Album
    			|_m_|AlbumId|TrackId|
    			+---+-------+-------+
    			|<  |456    |345    |
    			|>  |!NULL! |345    |    
    							
    • The thing we are testing
    • What we did
    • What we expected to happen

    Naming Conventions make custom messages redundant


    A new feature

    CREATE FUNCTION GetTracksPurchasedByCustomer(@CustomerId INT)
    RETURNS TABLE
    AS RETURN
    ( 
      SELECT    Track.Name AS TrackName ,
                CAST(Invoice.InvoiceDate AS DATE) AS InvoiceDate ,
                InvoiceLine.UnitPrice
      FROM      Invoice
                INNER JOIN InvoiceLine 
                ON Invoice.InvoiceId = InvoiceLine.InvoiceId
                INNER JOIN Track 
                ON InvoiceLine.TrackId = Track.TrackID
                WHERE Invoice.CustomerId = @CustomerId
    
    )
    

    Testing 1, 2, 3 (or 0, 1, n )

    CREATE PROCEDURE 
    GetTracksPurchasedByCustomer.[test no purchases returns empty result set] 
    AS
    EXEC tSQLt.FakeTable 'dbo.Track';
    EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    EXEC tSQLt.FakeTable 'dbo.Invoice';
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
    VALUES (123, 'Under The Moon of Love', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
    VALUES (234, 123, 888.88);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
    VALUES (234, 456, '2016-05-02');
    
    SELECT * INTO Actual FROM dbo.GetTracksPurchasedByCustomer(457);
    
    EXEC tSQLt.AssertEmptyTable 'Actual';
                            

    Testing 1, 2, 3 (or 0, 1, n )

    CREATE PROCEDURE 
    GetTracksPurchasedByCustomer.[test no purchases returns empty result set] 
    AS
    EXEC tSQLt.FakeTable 'dbo.Track';
    EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    EXEC tSQLt.FakeTable 'dbo.Invoice';
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
    VALUES (123, 'Under The Moon of Love', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
    VALUES (234, 123, 888.88);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
    VALUES (234, 456, '2016-05-02');
    
    SELECT * INTO Actual FROM dbo.GetTracksPurchasedByCustomer(457);
    
    EXEC tSQLt.AssertEmptyTable 'Actual';
                            

    Testing 1, 2, 3 (or 0, 1, n )

    CREATE PROCEDURE 
    GetTracksPurchasedByCustomer.[test no purchases returns empty result set] 
    AS
    EXEC tSQLt.FakeTable 'dbo.Track';
    EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    EXEC tSQLt.FakeTable 'dbo.Invoice';
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
    VALUES (123, 'Under The Moon of Love', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
    VALUES (234, 123, 888.88);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
    VALUES (234, 456, '2016-05-02');
    
    SELECT * INTO Actual FROM dbo.GetTracksPurchasedByCustomer(457);
    
    EXEC tSQLt.AssertEmptyTable 'Actual';
                            

    Testing 1, 2, 3 (or 0, 1, n )

    CREATE PROCEDURE 
    GetTracksPurchasedByCustomer.[test no purchases returns empty result set] 
    AS
    EXEC tSQLt.FakeTable 'dbo.Track';
    EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    EXEC tSQLt.FakeTable 'dbo.Invoice';
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
    VALUES (123, 'Under The Moon of Love', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
    VALUES (234, 123, 888.88);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
    VALUES (234, 456, '2016-05-02');
    
    SELECT * INTO Actual FROM dbo.GetTracksPurchasedByCustomer(457);
    
    EXEC tSQLt.AssertEmptyTable 'Actual';
                            

    Testing 1, 2, 3 (or 0, 1, n )

    CREATE PROCEDURE 
    GetTracksPurchasedByCustomer.[test single purchase returns one row]
    AS
    EXEC tSQLt.FakeTable 'dbo.Track';
    EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    EXEC tSQLt.FakeTable 'dbo.Invoice';
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
        VALUES (123, 'Under The Moon of Love', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
        VALUES (234, 123, 888.88);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
        VALUES (234, 456, '2016-05-02');
    
    SELECT * INTO Actual FROM dbo.GetTracksPurchasedByCustomer(456);
    SELECT * INTO Expected FROM (VALUES
    ('Under The Moon of Love', CAST('2016-05-02' AS DATE), 888.88)) 
    AS t(TrackName, InvoiceDate, UnitPrice);
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual';                        

    Testing 1, 2, 3 (or 0, 1, n )

    CREATE PROCEDURE 
    GetTracksPurchasedByCustomer.[test single purchase returns one row]
    AS
    EXEC tSQLt.FakeTable 'dbo.Track';
    EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    EXEC tSQLt.FakeTable 'dbo.Invoice';
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
        VALUES (123, 'Under The Moon of Love', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
        VALUES (234, 123, 888.88);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
        VALUES (234, 456, '2016-05-02');
    
    SELECT * INTO Actual FROM dbo.GetTracksPurchasedByCustomer(456);
    SELECT * INTO Expected FROM (VALUES
    ('Under The Moon of Love', CAST('2016-05-02' AS DATE), 888.88)) 
    AS t(TrackName, InvoiceDate, UnitPrice);
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual';                        

    Testing 1, 2, 3 (or 0, 1, n )

    CREATE PROCEDURE 
    GetTracksPurchasedByCustomer.[test single purchase returns one row]
    AS
    EXEC tSQLt.FakeTable 'dbo.Track';
    EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    EXEC tSQLt.FakeTable 'dbo.Invoice';
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
        VALUES (123, 'Under The Moon of Love', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
        VALUES (234, 123, 888.88);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
        VALUES (234, 456, '2016-05-02');
    
    SELECT * INTO Actual FROM dbo.GetTracksPurchasedByCustomer(456);
    SELECT * INTO Expected FROM (VALUES
    ('Under The Moon of Love', CAST('2016-05-02' AS DATE), 888.88)) 
    AS t(TrackName, InvoiceDate, UnitPrice);
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual';                        

    Testing 1, 2, 3 (or 0, 1, n )

    CREATE PROCEDURE 
    GetTracksPurchasedByCustomer.[test single purchase returns one row]
    AS
    EXEC tSQLt.FakeTable 'dbo.Track';
    EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    EXEC tSQLt.FakeTable 'dbo.Invoice';
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
        VALUES (123, 'Under The Moon of Love', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
        VALUES (234, 123, 888.88);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
        VALUES (234, 456, '2016-05-02');
    
    SELECT * INTO Actual FROM dbo.GetTracksPurchasedByCustomer(456);
    SELECT * INTO Expected FROM (VALUES
    ('Under The Moon of Love', CAST('2016-05-02' AS DATE), 888.88)) 
    AS t(TrackName, InvoiceDate, UnitPrice);
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual';                        

    Testing 1, 2, 3 (or 0, 1, n )

    CREATE PROCEDURE 
    GetTracksPurchasedByCustomer.[test separate purchases return separate rows]
    AS
    EXEC tSQLt.FakeTable 'dbo.Track';
    EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    EXEC tSQLt.FakeTable 'dbo.Invoice';
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
        VALUES (123, 'Under The Moon of Love', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
        VALUES (234, 123, 888.88);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
        VALUES (234, 456, '2016-05-02');
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
        VALUES (789, 'Hey Rock and Roll', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
        VALUES (567, 789, 777.77);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
        VALUES (567, 456, '2016-05-02');
    
    
    SELECT * INTO Actual FROM dbo.GetTracksPurchasedByCustomer(456);
    SELECT * INTO Expected FROM (VALUES
    ('Under The Moon of Love', CAST('2016-05-02' AS DATE), 888.88),
    ('Hey Rock and Roll', CAST('2016-05-02' AS DATE), 777.77)
    
    ) 
    AS t(TrackName, InvoiceDate, UnitPrice);
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual';
                          

    Testing 1, 2, 3 (or 0, 1, n )

    CREATE PROCEDURE 
    GetTracksPurchasedByCustomer.[test separate purchases return separate rows]
    AS
    EXEC tSQLt.FakeTable 'dbo.Track';
    EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    EXEC tSQLt.FakeTable 'dbo.Invoice';
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
        VALUES (123, 'Under The Moon of Love', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
        VALUES (234, 123, 888.88);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
        VALUES (234, 456, '2016-05-02');
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
        VALUES (789, 'Hey Rock and Roll', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
        VALUES (567, 789, 777.77);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
        VALUES (567, 456, '2016-05-02');
    
    
    SELECT * INTO Actual FROM dbo.GetTracksPurchasedByCustomer(456);
    SELECT * INTO Expected FROM (VALUES
    ('Under The Moon of Love', CAST('2016-05-02' AS DATE), 888.88),
    ('Hey Rock and Roll', CAST('2016-05-02' AS DATE), 777.77)
    
    ) 
    AS t(TrackName, InvoiceDate, UnitPrice);
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual';
                          

    Testing 1, 2, 3 (or 0, 1, n )

    CREATE PROCEDURE 
    GetTracksPurchasedByCustomer.[test separate purchases return separate rows]
    AS
    EXEC tSQLt.FakeTable 'dbo.Track';
    EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    EXEC tSQLt.FakeTable 'dbo.Invoice';
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
        VALUES (123, 'Under The Moon of Love', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
        VALUES (234, 123, 888.88);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
        VALUES (234, 456, '2016-05-02');
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
        VALUES (789, 'Hey Rock and Roll', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
        VALUES (567, 789, 777.77);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
        VALUES (567, 456, '2016-05-02');
    
    
    SELECT * INTO Actual FROM dbo.GetTracksPurchasedByCustomer(456);
    SELECT * INTO Expected FROM (VALUES
    ('Under The Moon of Love', CAST('2016-05-02' AS DATE), 888.88),
    ('Hey Rock and Roll', CAST('2016-05-02' AS DATE), 777.77)
    
    ) 
    AS t(TrackName, InvoiceDate, UnitPrice);
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual';
                          

    Testing 1, 2, 3 (or 0, 1, n )

    CREATE PROCEDURE 
    GetTracksPurchasedByCustomer.[test separate purchases return separate rows]
    AS
    EXEC tSQLt.FakeTable 'dbo.Track';
    EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    EXEC tSQLt.FakeTable 'dbo.Invoice';
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
        VALUES (123, 'Under The Moon of Love', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
        VALUES (234, 123, 888.88);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
        VALUES (234, 456, '2016-05-02');
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
        VALUES (789, 'Hey Rock and Roll', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
        VALUES (567, 789, 777.77);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
        VALUES (567, 456, '2016-05-02');
    
    
    SELECT * INTO Actual FROM dbo.GetTracksPurchasedByCustomer(456);
    SELECT * INTO Expected FROM (VALUES
    ('Under The Moon of Love', CAST('2016-05-02' AS DATE), 888.88),
    ('Hey Rock and Roll', CAST('2016-05-02' AS DATE), 777.77)
    
    ) 
    AS t(TrackName, InvoiceDate, UnitPrice);
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual';
                          

    Testing 1, 2, 3 (or 0, 1, n )

    CREATE PROCEDURE 
    GetTracksPurchasedByCustomer.[test separate purchases return separate rows]
    AS
    EXEC tSQLt.FakeTable 'dbo.Track';
    EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    EXEC tSQLt.FakeTable 'dbo.Invoice';
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
        VALUES (123, 'Under The Moon of Love', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
        VALUES (234, 123, 888.88);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
        VALUES (234, 456, '2016-05-02');
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
        VALUES (789, 'Hey Rock and Roll', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
        VALUES (567, 789, 777.77);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
        VALUES (567, 456, '2016-05-02');
    
    
    SELECT * INTO Actual FROM dbo.GetTracksPurchasedByCustomer(456);
    SELECT * INTO Expected FROM (VALUES
    ('Under The Moon of Love', CAST('2016-05-02' AS DATE), 888.88),
    ('Hey Rock and Roll', CAST('2016-05-02' AS DATE), 777.77)
    
    ) 
    AS t(TrackName, InvoiceDate, UnitPrice);
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual';
                          

    Test Setup Procedure

    Called before every test

    CREATE PROCEDURE GetTracksPurchasedByCustomer.Setup
    AS
    EXEC tSQLt.FakeTable 'dbo.Track';
    EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    EXEC tSQLt.FakeTable 'dbo.Invoice';
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) VALUES (123, 'Dancin'' Party', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) VALUES (234, 123, 666.66);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) VALUES (234, 457, '2016-05-02');
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) VALUES (789, 'Hey Rock and Roll', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) VALUES (567, 789, 777.77);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) VALUES (567, 457, '2016-05-02');
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) VALUES (432, 'Under The Moon of Love', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) VALUES (678, 432, 888.88);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) VALUES (678, 456, '2016-05-02');

    Test Setup Procedure

    Called before every test

    CREATE PROCEDURE GetTracksPurchasedByCustomer.Setup
    AS
    EXEC tSQLt.FakeTable 'dbo.Track';
    EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    EXEC tSQLt.FakeTable 'dbo.Invoice';
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) VALUES (123, 'Dancin'' Party', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) VALUES (234, 123, 666.66);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) VALUES (234, 457, '2016-05-02');
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) VALUES (789, 'Hey Rock and Roll', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) VALUES (567, 789, 777.77);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) VALUES (567, 457, '2016-05-02');
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) VALUES (432, 'Under The Moon of Love', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) VALUES (678, 432, 888.88);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) VALUES (678, 456, '2016-05-02');
    

    Test Setup Procedure

    Called before every test

    CREATE PROCEDURE GetTracksPurchasedByCustomer.Setup
    AS
    EXEC tSQLt.FakeTable 'dbo.Track';
    EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    EXEC tSQLt.FakeTable 'dbo.Invoice';
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) VALUES (123, 'Dancin'' Party', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) VALUES (234, 123, 666.66);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) VALUES (234, 457, '2016-05-02');
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) VALUES (789, 'Hey Rock and Roll', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) VALUES (567, 789, 777.77);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) VALUES (567, 457, '2016-05-02');
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) VALUES (432, 'Under The Moon of Love', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) VALUES (678, 432, 888.88);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) VALUES (678, 456, '2016-05-02');
    

    Test Setup Procedure

    Called before every test

    CREATE PROCEDURE GetTracksPurchasedByCustomer.Setup
    AS
    EXEC tSQLt.FakeTable 'dbo.Track';
    EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    EXEC tSQLt.FakeTable 'dbo.Invoice';
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) VALUES (123, 'Dancin'' Party', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) VALUES (234, 123, 666.66);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) VALUES (234, 457, '2016-05-02');
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) VALUES (789, 'Hey Rock and Roll', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) VALUES (567, 789, 777.77);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) VALUES (567, 457, '2016-05-02');
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) VALUES (432, 'Under The Moon of Love', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) VALUES (678, 432, 888.88);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) VALUES (678, 456, '2016-05-02')
    

    Test Setup Procedure

    Called before every test

    CREATE PROCEDURE GetTracksPurchasedByCustomer.Setup
    AS
    EXEC tSQLt.FakeTable 'dbo.Track';
    EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    EXEC tSQLt.FakeTable 'dbo.Invoice';
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) VALUES (123, 'Dancin'' Party', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) VALUES (234, 123, 666.66);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) VALUES (234, 457, '2016-05-02');
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) VALUES (789, 'Hey Rock and Roll', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) VALUES (567, 789, 777.77);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) VALUES (567, 457, '2016-05-02');
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) VALUES (432, 'Under The Moon of Love', 999.99);
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) VALUES (678, 432, 888.88);
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) VALUES (678, 456, '2016-05-02')
    

    Test using setup procedure

    CREATE PROCEDURE GetTracksPurchasedByCustomer.[test separate purchases return separate rows]
    AS
    --??? 
    
    --Act
    SELECT * INTO Actual FROM dbo.GetTracksPurchasedByCustomer(457);
    
    --Assert
    SELECT * INTO Expected FROM (VALUES
    ('Dancin'' Party', CAST('2016-05-02' AS DATE), 666.66),
    ('Hey Rock and Roll', CAST('2016-05-02' AS DATE), 777.77)
    
    ) 
    AS t(TrackName, InvoiceDate, UnitPrice);
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual';
                    

    Test using setup procedure

    CREATE PROCEDURE GetTracksPurchasedByCustomer.[test separate purchases return separate rows]
    AS
    --??? 
    
    --Act
    SELECT * INTO Actual FROM dbo.GetTracksPurchasedByCustomer(457);
    
    --Assert
    SELECT * INTO Expected FROM (VALUES
    ('Dancin'' Party', CAST('2016-05-02' AS DATE), 666.66),
    ('Hey Rock and Roll', CAST('2016-05-02' AS DATE), 777.77)
    
    ) 
    AS t(TrackName, InvoiceDate, UnitPrice);
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual';
    				

    A better way

    CREATE PROCEDURE 
    GetTracksPurchasedByCustomer.[Fake Track, Invoice, and Invoice Line]
    AS
    EXEC tSQLt.FakeTable 'dbo.Track';
    EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    EXEC tSQLt.FakeTable 'dbo.Invoice';
    

    A better way

    CREATE PROCEDURE 
    GetTracksPurchasedByCustomer.[Fake Track, Invoice, and Invoice Line]
    AS
    EXEC tSQLt.FakeTable 'dbo.Track';
    EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    EXEC tSQLt.FakeTable 'dbo.Invoice';
    

    A better way

    CREATE PROCEDURE 
    GetTracksPurchasedByCustomer.[Fake Track, Invoice, and Invoice Line]
    AS
    EXEC tSQLt.FakeTable 'dbo.Track';
    EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    EXEC tSQLt.FakeTable 'dbo.Invoice';
    

    A Test Data Builder

    CREATE PROCEDURE 
    GetTracksPurchasedByCustomer.[Create Invoice For One Track]
    @TrackName nvarchar(200) = NULL, @TrackPrice numeric(10, 2) = NULL,
    @InvoiceLinePrice numeric(10, 2) = NULL, @InvoiceDate date = NULL, 
    @Composer nvarchar(220) = NULL, @Quantity int = NULL,
    --blah blah more params, all defaulted to NULL
    
    @CustomerID INT OUTPUT
    
    AS
    DECLARE @TrackID INT = ABS(Checksum(NewID()) % 1000);
    DECLARE @InvoiceID INT = ABS(Checksum(NewID()) % 1000);
    SET @CustomerID  = ABS(Checksum(NewID()) % 1000);
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
    VALUES (@TrackID, @TrackName, @TrackPrice);
    
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
    VALUES (@InvoiceID, @TrackID, @InvoiceLinePrice);
    
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
    VALUES (@InvoiceID, @CustomerID, @InvoiceDate);
    

    A Test Data Builder

    CREATE PROCEDURE 
    GetTracksPurchasedByCustomer.[Create Invoice For One Track] 
    @TrackName nvarchar(200) = NULL, @TrackPrice numeric(10, 2) = NULL,
    @InvoiceLinePrice numeric(10, 2) = NULL, @InvoiceDate date = NULL, 
    @Composer nvarchar(220) = NULL, @Quantity int = NULL,
    --blah blah more params, all defaulted to NULL
    
    @CustomerID INT OUTPUT
    
    AS
    DECLARE @TrackID INT = ABS(Checksum(NewID()) % 1000);
    DECLARE @InvoiceID INT = ABS(Checksum(NewID()) % 1000);
    SET @CustomerID  = ABS(Checksum(NewID()) % 1000);
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
    VALUES (@TrackID, @TrackName, @TrackPrice);
    
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
    VALUES (@InvoiceID, @TrackID, @InvoiceLinePrice);
    
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
    VALUES (@InvoiceID, @CustomerID, @InvoiceDate);
    

    A Test Data Builder

    CREATE PROCEDURE 
    GetTracksPurchasedByCustomer.[Create Invoice For One Track] 
    @TrackName nvarchar(200) = NULL, @TrackPrice numeric(10, 2) = NULL,
    @InvoiceLinePrice numeric(10, 2) = NULL, @InvoiceDate date = NULL, 
    @Composer nvarchar(220) = NULL, @Quantity int = NULL,
    --blah blah more params, all defaulted to NULL
    
    @CustomerID INT OUTPUT
    
    AS
    DECLARE @TrackID INT = ABS(Checksum(NewID()) % 1000);
    DECLARE @InvoiceID INT = ABS(Checksum(NewID()) % 1000);
    SET @CustomerID  = ABS(Checksum(NewID()) % 1000);
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
    VALUES (@TrackID, @TrackName, @TrackPrice);
    
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
    VALUES (@InvoiceID, @TrackID, @InvoiceLinePrice);
    
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
    VALUES (@InvoiceID, @CustomerID, @InvoiceDate);
    

    A Test Data Builder

    CREATE PROCEDURE 
    GetTracksPurchasedByCustomer.[Create Invoice For One Track] 
    @TrackName nvarchar(200) = NULL, @TrackPrice numeric(10, 2) = NULL,
    @InvoiceLinePrice numeric(10, 2) = NULL, @InvoiceDate date = NULL, 
    @Composer nvarchar(220) = NULL, @Quantity int = NULL,
    --blah blah more params, all defaulted to NULL
    
    @CustomerID INT OUTPUT
    
    AS
    DECLARE @TrackID INT = ABS(Checksum(NewID()) % 1000);
    DECLARE @InvoiceID INT = ABS(Checksum(NewID()) % 1000);
    SET @CustomerID  = ABS(Checksum(NewID()) % 1000);
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
    VALUES (@TrackID, @TrackName, @TrackPrice);
    
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
    VALUES (@InvoiceID, @TrackID, @InvoiceLinePrice);
    
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
    VALUES (@InvoiceID, @CustomerID, @InvoiceDate);
    

    A Test Data Builder

    CREATE PROCEDURE 
    GetTracksPurchasedByCustomer.[Create Invoice For One Track] 
    @TrackName nvarchar(200) = NULL, @TrackPrice numeric(10, 2) = NULL,
    @InvoiceLinePrice numeric(10, 2) = NULL, @InvoiceDate date = NULL, 
    @Composer nvarchar(220) = NULL, @Quantity int = NULL,
    --blah blah more params, all defaulted to NULL
    
    @CustomerID INT OUTPUT
    
    AS
    DECLARE @TrackID INT = ABS(Checksum(NewID()) % 1000);
    DECLARE @InvoiceID INT = ABS(Checksum(NewID()) % 1000);
    SET @CustomerID  = ABS(Checksum(NewID()) % 1000);
    
    INSERT INTO dbo.Track (TrackId, Name, UnitPrice) 
    VALUES (@TrackID, @TrackName, @TrackPrice);
    
    INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice) 
    VALUES (@InvoiceID, @TrackID, @InvoiceLinePrice);
    
    INSERT INTO dbo.Invoice(InvoiceId, CustomerId, InvoiceDate) 
    VALUES (@InvoiceID, @CustomerID, @InvoiceDate);
    

    The revised test

    
    CREATE PROCEDURE GetTracksPurchasedByCustomer.[test single purchase returns one row]
    AS
    --Arrange
    EXEC GetTracksPurchasedByCustomer.[Fake Track, Invoice, and Invoice Line]
    DECLARE @CustomerID int;
    EXEC GetTracksPurchasedByCustomer.[Create Invoice For One Track] 
        @TrackName = 'Under The Moon of Love',
        @InvoiceDate = '2016-05-02', 
        @InvoiceLinePrice = '888.88',
        @CustomerId = @CustomerId OUTPUT;
    --Act
    SELECT * INTO Actual 
    FROM dbo.GetTracksPurchasedByCustomer(@CustomerId);
    --Assert
    SELECT * INTO Expected FROM (VALUES
    ('Under The Moon of Love', CAST('2016-05-02' AS DATE), 888.88)) 
    AS t(TrackName, InvoiceDate, UnitPrice);
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual';
    
    

    The revised test

    
    CREATE PROCEDURE GetTracksPurchasedByCustomer.[test single purchase returns one row]
    AS
    --Arrange
    EXEC GetTracksPurchasedByCustomer.[Fake Track, Invoice, and Invoice Line]
    DECLARE @CustomerID int;
    EXEC GetTracksPurchasedByCustomer.[Create Invoice For One Track] 
    	@TrackName = 'Under The Moon of Love',
    	@InvoiceDate = '2016-05-02', 
    	@InvoiceLinePrice = '888.88',
    	@CustomerId = @CustomerId OUTPUT;
    --Act
    SELECT * INTO Actual 
    FROM dbo.GetTracksPurchasedByCustomer(@CustomerId);
    --Assert
    SELECT * INTO Expected FROM (VALUES
    ('Under The Moon of Love', CAST('2016-05-02' AS DATE), 888.88)) 
    AS t(TrackName, InvoiceDate, UnitPrice);
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual';
    
    

    Use helper procedures and data builders, not setup procedures


    Dating disasters...

    Table dbo.PlayListTrack

    PlaylistId TrackId DateAdded
    PK, FK, int, not null PK, FK, int, not null datetime2, not null
    
    CREATE PROCEDURE AddTrackToPlaylist @TrackID INT, @PlaylistID INT
    AS
    MERGE dbo.PlaylistTrack as t
    
    USING (VALUES (@TrackID, @PlaylistID)) as s(TrackID, PlaylistID)
        ON t.TrackID = s.TrackID AND t.PlaylistID = s.PlaylistID
    
    WHEN NOT MATCHED THEN INSERT (PlaylistID, TrackID, DateAdded) 
        VALUES (s.PlaylistID, s.TrackID, SYSUTCDATETIME())
    
    WHEN MATCHED THEN UPDATE SET DateAdded = SYSUTCDATETIME();

    What goes in the ???

    
    CREATE PROCEDURE 
    AddTrackToPlaylist[test valid track and playlist inserted]
    AS
    --Arrange
    EXEC tSQLt.FakeTable 'dbo.PlaylistTrack';
    
    --Act
    EXEC AddTrackToPlaylist @TrackId = 66, @PlayListId = 67;
    
    --Assert
    SELECT * INTO Expected 
        FROM (VALUES(67, 66, '???')) as 
        t(PlaylistID, TrackID, DateAdded);
    
    SELECT PlayListID, TrackID, DateAdded into Actual 
        from dbo.PlaylistTrack;
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual';

    The quick solution

    
    CREATE PROCEDURE 
    AddTrackToPlaylist[test valid track and playlist inserted]
    AS
    --Arrange
    EXEC tSQLt.FakeTable 'dbo.PlaylistTrack';
    
    --Act
    EXEC AddTrackToPlaylist @TrackId = 66, @PlayListId = 67;
    
    --Assert
    SELECT * INTO Expected FROM 
        (VALUES(67, 66, CAST(SYSUTCDATETIME() AS Date))) as 
            t(PlaylistID, TrackID, DateAdded);
    SELECT PlayListID, TrackID, CAST(DateAdded AS Date) as DateAdded 
        into Actual from dbo.PlaylistTrack;
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual';

    Refactoring the procedure

    
    CREATE PROCEDURE AddTrackToPlaylist @TrackID INT, @PlaylistID INT, 
    @DateAdded datetime2 = NULL
    AS
    
    SET @DateAdded = ISNULL(@DateAdded, SYSUTCDATETIME());
    
    MERGE dbo.PlaylistTrack as t
    USING (VALUES (@TrackID, @PlaylistID)) as s(TrackID, PlaylistID)
    ON t.TrackID = s.TrackID AND t.PlaylistID = s.PlaylistID
    
    WHEN NOT MATCHED THEN INSERT (PlaylistID, TrackID, DateAdded) 
        	VALUES (s.PlaylistID, s.TrackID, @DateAdded)
    
    WHEN MATCHED THEN UPDATE SET DateAdded = @DateAdded;

    Refactoring the procedure

    
    CREATE PROCEDURE AddTrackToPlaylist @TrackID INT, @PlaylistID INT, 
    @DateAdded datetime2 = NULL
    AS
    
    SET @DateAdded = ISNULL(@DateAdded, SYSUTCDATETIME());
    
    MERGE dbo.PlaylistTrack as t
    USING (VALUES (@TrackID, @PlaylistID)) as s(TrackID, PlaylistID)
    ON t.TrackID = s.TrackID AND t.PlaylistID = s.PlaylistID
    
    WHEN NOT MATCHED THEN INSERT (PlaylistID, TrackID, DateAdded) 
    	VALUES (s.PlaylistID, s.TrackID, @DateAdded)
    
    WHEN MATCHED THEN UPDATE SET DateAdded = @DateAdded;

    Refactoring the procedure

    
    CREATE PROCEDURE AddTrackToPlaylist @TrackID INT, @PlaylistID INT, 
    @DateAdded datetime2 = NULL
    AS
    
    SET @DateAdded = ISNULL(@DateAdded, SYSUTCDATETIME());
    
    MERGE dbo.PlaylistTrack as t
    USING (VALUES (@TrackID, @PlaylistID)) as s(TrackID, PlaylistID)
    ON t.TrackID = s.TrackID AND t.PlaylistID = s.PlaylistID
    
    WHEN NOT MATCHED THEN INSERT (PlaylistID, TrackID, DateAdded) 
    	VALUES (s.PlaylistID, s.TrackID, @DateAdded)
    
    WHEN MATCHED THEN UPDATE SET DateAdded = @DateAdded;

    The updated test

    CREATE PROCEDURE 
    AddTrackToPlaylist.[test valid track and playlist inserted]
    AS
    --Arrange
    EXEC tSQLt.FakeTable 'dbo.PlaylistTrack';
    DECLARE @theTimeNow datetime2 = SYSUTCDATETIME();
    --Act
    EXEC AddTrackToPlaylist @TrackId = 66, @PlayListId = 67, 
        @DateAdded = @theTimeNow;
    
    --Assert
    SELECT * INTO Expected FROM (VALUES(67, 66, @theTimeNow)) 
        as t(PlaylistID, TrackID, DateAdded);
    SELECT PlayListID, TrackID, DateAdded into Actual 
        from dbo.PlaylistTrack;
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual';

    It's OK to rewrite the code to make it easier to test!


    Pass dependencies as optional parameters where possible

    The eierlegende Wollmilchsau

    
    CREATE PROCEDURE DoAllTheThings @DayOfWeek varchar(10)
    AS
    --Runs every night to perform various tasks
    BEGIN
    IF (@DayOfWeek = 'Monday')
    BEGIN
    	DELETE FROM Genre WHERE NOT EXISTS
    	(SELECT * FROM Track WHERE Track.GenreId = Genre.GenreId)
    
    END
    
    ELSE IF (@DayOfWeek = 'Wednesday')
    BEGIN
    	UPDATE Track SET UnitPrice = UnitPrice * 1.01;
    	UPDATE Customer SET PostalCode = GetPostalCodeFromExernalService(Address)
    	WHERE PostalCode IS NULL;
    END
    ELSE IF (@DayOfWeek = 'Thursday')
    BEGIN
    	;WITH Sales as(
    	SELECT Artist.ArtistID, Artist.Name, COUNT(InvoiceLineID) as Sales
    	FROM InvoiceLine JOIN Track ON InvoiceLine.TrackID = Track.TrackId
    	Join Album ON Track.AlbumId = Album.AlbumID
    	JOIN Artist ON Album.ArtistId = Artist.ArtistId
    	GROUP BY Artist.ArtistID, Artist.Name),
    	SalesRank as 
    
    	(SELECT ArtistId, Name,  Row_Number() over (order by sales desc) as Popularity FROM Sales
    	)
    
    	UPDATE Artist SET Popularity = (select popularity from SalesRank where SalesRank.ArtistId = Artist.ArtistId)
    END
    
    ELSE IF (@DayOfWeek = 'Friday')
    BEGIN
    	DECLARE c CURSOR for
    	SELECT Email FROM Employee
    	 WHERE (DATEDIFF(dd, getdate(), DATEADD(yyyy, DATEDIFF(yyyy, birthdate, getdate()) + 1, birthdate))) % 366 <= 7
    	 DECLARE @email varchar(60)
    	 open c
    	 FETCH NEXT FROM c INTO @email
    	 WHILE @@FETCH_STATUS = 0
    	 BEGIN
    		EXEC msdb..sp_send_dbmail @recipients = @email, @subject = 'Birthday Greetings', @body = 'Happy Birthday for next week'
    		FETCH NEXT FROM c INTO @email
    	 END
    	 CLOSE c
    	 DEALLOCATE c
    END
    
    ELSE
    BEGIN
    	EXEC xp_logevent 50234, 'I''m a pointless log message'
    END
    END

    The eierlegende Wollmilchsau

    
    CREATE PROCEDURE DoAllTheThings @DayOfWeek varchar(10)
    AS
    --Runs every night to perform various tasks
    BEGIN
    IF (@DayOfWeek = 'Monday')
    BEGIN
    	DELETE FROM Genre WHERE NOT EXISTS
    	(SELECT * FROM Track WHERE Track.GenreId = Genre.GenreId)
    
    END
    
    ELSE IF (@DayOfWeek = 'Wednesday')
    BEGIN
    	UPDATE Track SET UnitPrice = UnitPrice * 1.01;
    	UPDATE Customer SET PostalCode = GetPostalCodeFromExernalService(Address)
    	WHERE PostalCode IS NULL;
    END
    ELSE IF (@DayOfWeek = 'Thursday')
    BEGIN
    	;WITH Sales as(
    	SELECT Artist.ArtistID, Artist.Name, COUNT(InvoiceLineID) as Sales
    	FROM InvoiceLine JOIN Track ON InvoiceLine.TrackID = Track.TrackId
    	Join Album ON Track.AlbumId = Album.AlbumID
    	JOIN Artist ON Album.ArtistId = Artist.ArtistId
    	GROUP BY Artist.ArtistID, Artist.Name),
    	SalesRank as 
    
    	(SELECT ArtistId, Name,  Row_Number() over (order by sales desc) as Popularity FROM Sales
    	)
    
    	UPDATE Artist SET Popularity = (select popularity from SalesRank where SalesRank.ArtistId = rtist.ArtistId)
    END
    
    ELSE IF (@DayOfWeek = 'Friday')
    BEGIN
    	DECLARE c CURSOR for
    	SELECT Email FROM Employee
    	 WHERE (DATEDIFF(dd, getdate(), DATEADD(yyyy, DATEDIFF(yyyy, birthdate, getdate()) + 1, irthdate))) % 366 <= 7
    	 DECLARE @email varchar(60)
    	 open c
    	 FETCH NEXT FROM c INTO @email
    	 WHILE @@FETCH_STATUS = 0
    	 BEGIN
    		EXEC msdb..sp_send_dbmail @recipients = @email, @subject = 'Birthday Greetings', @body = 'Happy Birthday for next week'
    		FETCH NEXT FROM c INTO @email
    	 END
    	 CLOSE c
    	 DEALLOCATE c
    END
    
    ELSE
    BEGIN
    	EXEC xp_logevent 50234, 'I''m a pointless log message'
    END
    END

    Mock All the Things

    
    CREATE PROCEDURE DoAllTheThings.[Mock All the Things]   
    EXEC tSQLT.FakeTable 'Genre';
    EXEC tSQLT.FakeTable 'Track';
    EXEC tSQLT.FakeTable 'Employee';
    EXEC tSQLT.FakeTable 'Customer';
    EXEC tSQLT.FakeTable 'Artist';
    EXEC tSQLT.FakeTable 'InvoiceLine';
    
        

    Refactoring the Procedure

    CREATE PROCEDURE DoAllTheThings @DayOfWeek varchar(10)
    AS
    --Runs every night to perform various tasks
    BEGIN
    IF (@DayOfWeek = 'Monday')
    BEGIN
    	EXEC DeleteEmptyGenres;
    	EXEC UpdateArtistRankings;
    END
    ELSE IF (@DayOfWeek = 'Wednesday')
    BEGIN
    	EXEC IncreasePrices;
    	EXEC UpdateMissingPostcodes;
    END
    ELSE IF (@DayOfWeek = 'Thursday')
    BEGIN
    	EXEC UpdateArtistRankings;
    END
    
    ELSE IF (@DayOfWeek = 'Friday')
    BEGIN
    	EXEC SendBirthdayGreetings(7);
    END
    
    ELSE
    BEGIN
    	EXEC DoPointlessLogging;
    END
    END
        

    Saving time by testing more than one thing at once

    
    CREATE PROCEDURE DoAllTheThings.[test on Mondays we delete empty genres and update artist rankings but don't increase prices or update missing postcodes]
    AS
    EXEC tSQLt.SpyProcedure 'DeleteEmptyGenres';
    EXEC tSQLt.SpyProcedure 'UpdateArtistRankings';
    EXEC tSQLt.SpyProcedure 'IncreasePrices';
    EXEC tSQLt.SpyProcedure 'UpdateMissingPostcodes';
    
    
    EXEC DoAllTheThings 'Monday';
    
    
    DECLARE @executionCountDeleteEmptyGenres int = 
    (select COUNT(*) from DeleteEmptyGenres_SpyProcedureLog);
    
    DECLARE @executionCountUpdateArtistRankings int = 
    (select COUNT(*) from UpdateArtistRankings_SpyProcedureLog);
    
    DECLARE @executionCountIncreasePrices int = 
    (select COUNT(*) from IncreasePrices_SpyProcedureLog);
    
    DECLARE @executionCountUpdateMissingPostcodes int = 
    (select COUNT(*) from UpdateMissingPostcodes_SpyProcedureLog);
    
    EXEC tSQLt.AssertEquals 1, @executionCountDeleteEmptyGenres;
    EXEC tSQLt.AssertEquals 1, @executionCountUpdateArtistRankings;
    EXEC tSQLt.AssertEquals 0, @executionCountIncreasePrices;
    EXEC tSQLt.AssertEquals 0, @executionCountUpdateMissingPostcodes;
    

    Saving time by testing more than one thing at once

    
    CREATE PROCEDURE DoAllTheThings.[test on Mondays we delete empty genres and update artist rankings but don't increase prices or update missing postcodes]
    AS
    EXEC tSQLt.SpyProcedure 'DeleteEmptyGenres';
    EXEC tSQLt.SpyProcedure 'UpdateArtistRankings';
    EXEC tSQLt.SpyProcedure 'IncreasePrices';
    EXEC tSQLt.SpyProcedure 'UpdateMissingPostcodes';
    
    
    EXEC DoAllTheThings 'Monday';
    
    
    DECLARE @executionCountDeleteEmptyGenres int = 
    (select COUNT(*) from DeleteEmptyGenres_SpyProcedureLog);
    
    DECLARE @executionCountUpdateArtistRankings int = 
    (select COUNT(*) from UpdateArtistRankings_SpyProcedureLog);
    
    DECLARE @executionCountIncreasePrices int = 
    (select COUNT(*) from IncreasePrices_SpyProcedureLog);
    
    DECLARE @executionCountUpdateMissingPostcodes int = 
    (select COUNT(*) from UpdateMissingPostcodes_SpyProcedureLog);
    
    EXEC tSQLt.AssertEquals 1, @executionCountDeleteEmptyGenres;
    EXEC tSQLt.AssertEquals 1, @executionCountUpdateArtistRankings;
    EXEC tSQLt.AssertEquals 0, @executionCountIncreasePrices;
    EXEC tSQLt.AssertEquals 0, @executionCountUpdateMissingPostcodes;
    

    Saving time by testing more than one thing at once

    
    CREATE PROCEDURE DoAllTheThings.[test on Mondays we delete empty genres and update artist rankings but don't increase prices or update missing postcodes]
    AS
    EXEC tSQLt.SpyProcedure 'DeleteEmptyGenres';
    EXEC tSQLt.SpyProcedure 'UpdateArtistRankings';
    EXEC tSQLt.SpyProcedure 'IncreasePrices';
    EXEC tSQLt.SpyProcedure 'UpdateMissingPostcodes';
    
    
    EXEC DoAllTheThings 'Monday';
    
    
    DECLARE @executionCountDeleteEmptyGenres int = 
    (select COUNT(*) from DeleteEmptyGenres_SpyProcedureLog);
    
    DECLARE @executionCountUpdateArtistRankings int = 
    (select COUNT(*) from UpdateArtistRankings_SpyProcedureLog);
    
    DECLARE @executionCountIncreasePrices int = 
    (select COUNT(*) from IncreasePrices_SpyProcedureLog);
    
    DECLARE @executionCountUpdateMissingPostcodes int = 
    (select COUNT(*) from UpdateMissingPostcodes_SpyProcedureLog);
    
    EXEC tSQLt.AssertEquals 1, @executionCountDeleteEmptyGenres;
    EXEC tSQLt.AssertEquals 1, @executionCountUpdateArtistRankings;
    EXEC tSQLt.AssertEquals 0, @executionCountIncreasePrices;
    EXEC tSQLt.AssertEquals 0, @executionCountUpdateMissingPostcodes;
    

    Saving time by testing more than one thing at once

    
    CREATE PROCEDURE DoAllTheThings.[test on Mondays we delete empty genres and update artist rankings but don't increase prices or update missing postcodes]
    AS
    EXEC tSQLt.SpyProcedure 'DeleteEmptyGenres';
    EXEC tSQLt.SpyProcedure 'UpdateArtistRankings';
    EXEC tSQLt.SpyProcedure 'IncreasePrices';
    EXEC tSQLt.SpyProcedure 'UpdateMissingPostcodes';
    
    
    EXEC DoAllTheThings 'Monday';
    
    
    DECLARE @executionCountDeleteEmptyGenres int = 
    (select COUNT(*) from DeleteEmptyGenres_SpyProcedureLog);
    
    DECLARE @executionCountUpdateArtistRankings int = 
    (select COUNT(*) from UpdateArtistRankings_SpyProcedureLog);
    
    DECLARE @executionCountIncreasePrices int = 
    (select COUNT(*) from IncreasePrices_SpyProcedureLog);
    
    DECLARE @executionCountUpdateMissingPostcodes int = 
    (select COUNT(*) from UpdateMissingPostcodes_SpyProcedureLog);
    
    DECLARE @totalExecutionCount int = @executionCountDeleteEmptyGenres + @executionCountUpdateArtistRankings + 
    	@executionCountIncreasePrices + @executionCountUpdateMissingPostcodes 
    
    EXEC tSQLt.AssertEquals 2, @totalExecutionCount;
    	

    A specific Test

    CREATE PROCEDURE DoAllTheThings.[test On Thursday we update artist rankings]
    AS
    --arrange
    EXEC tsqlt.SpyProcedure 'UpdateArtistRankings'
    
    --act
    
    EXEC DoAllTheThings 'Thursday';
    
    --assert
    
    DECLARE @executionCount int = 
        (select COUNT(*) from UpdateArtistRankings_SpyProcedureLog);
    
    EXEC tSQLt.AssertEquals 1, @executionCount;
        

    A negative Test

    CREATE PROCEDURE 
    DoAllTheThings.[test On Thursday postcode update not called]
    AS
    --arrange
    EXEC tsqlt.SpyProcedure 'UpdateMissingPostcodes'
    
    --act
    
    EXEC DoAllTheThings 'Thursday';
    
    --assert
    
    DECLARE @executionCount int = 
        (select COUNT(*) from UpdateMissingPostcodes_SpyProcedureLog);
    
    EXEC tSQLt.AssertEquals 0, @executionCount;
        

    Wrapping the things we can't Mock

    EXEC xp_logevent 50234, 'I''m a pointless log message'
        
    CREATE PROCEDURE DoPointlessLogging
    AS
    BEGIN
    EXEC xp_logevent 50234, 'I''m a pointless log message'
    END
        

    Wrapping the things we can't Mock

    CREATE PROCEDURE DoAllTheThings.
    [test On Thursday pointless logging not called]
    AS
    --arrange
    EXEC tsqlt.SpyProcedure 'DoPointlessLogging'
    
    --act
    
    EXEC DoAllTheThings 'Thursday';
    
    --assert
    
    DECLARE @executionCount int = 
        (select COUNT(*) from DoPointlessLogging_SpyProcedureLog);
    
    EXEC tSQLt.AssertEquals 0, @executionCount;
        

    Mocking the whole world is a sign that the procedure is doing too much


    If you have difficulty saying what the test condition is, you're going to have difficulty maintaining it

    Expecting the worst

    
    				CREATE PROCEDURE AddLineToExistingInvoice @InvoiceId INT, @TrackId INT, @UnitPrice numeric(10, 2), @Quantity int
    				AS
    				BEGIN
    				INSERT INTO dbo.InvoiceLine(InvoiceId, TrackId, UnitPrice, Quantity)
    				VALUES
    				(@InvoiceId, @TrackId, @UnitPrice, @Quantity);
    				END
    				GO	
    
    
    		
    
    				CREATE PROCEDURE AddLineToExistingInvoice.[test non-existent invoice throws exception]
    				AS
    				
    				EXEC tSQLt.FakeTable 'dbo.Invoice';
    				EXEC tSQLt.FakeTable 'dbo.InvoiceLine';
    				EXEC tSQLt.ApplyConstraint 'dbo.InvoiceLine', 'FK_InvoiceLineInvoiceId';
    				
    				EXEC tSQLt.ExpectException;
    				
    				EXEC AddLineToExistingInvoice @InvoiceID = 123, @TrackId = 345, @UnitPrice = 99.99, @Quantity = 2
    				
    				GO
    
    
    		
    
    				+----------------------+
    				|Test Execution Summary|
    				+----------------------+
    				 
    				|No|Test Case Name                                                         |Dur(ms)|Result |
    				+--+-----------------------------------------------------------------------+-------+-------+
    				|1 |[AddLineToExistingInvoice].[test non-existent invoice throws exception]|   1217|Success|
    				-----------------------------------------------------------------------------
    				Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored.
    				-----------------------------------------------------------------------------
    				
    
    		

    Expecting the worst

    
    CREATE PROCEDURE AddNewAlbum @ArtistName nvarchar(120), @AlbumName nvarchar(160)
    AS
    
    DECLARE @ArtistID int;
    
    SELECT @ArtistID = ArtistId FROM Artist WHERE Name = @ArtistName;
    
    IF (@ArtistID IS NULL)
    BEGIN
    	Exec LogError @message = 'The artist name does not exist';
    	THROW 54321, 'The artist name does not exist', 1;
    END
    ELSE
    BEGIN
    INSERT INTO Album (Title, ArtistID) VALUES (@AlbumName, @ArtistID);
    END   
        

    Expecting Exceptions

    
    CREATE PROCEDURE AddNewAlbum.[Test missing artist throws exception and writes log]
    AS
    EXEC tSQLT.FakeTable 'Artist';
    EXEC tSQLt.FakeTable 'Album';
    EXEC tSQLt.SpyProcedure 'LogError'
    
    EXEC tsqlt.ExpectException;
    EXEC AddNewAlbum 'Showaddywaddy', 'Trocadero';
    
    SELECT 'The artist name does not exist' as message into Expected;
    SELECT message into Actual from LogError_SpyProcedureLog ;
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual'; 
        
    This test passes

    Expecting Exceptions

    
    CREATE PROCEDURE AddNewAlbum.[Test missing artist throws exception and writes log]
    AS
    EXEC tSQLT.FakeTable 'Artist';
    EXEC tSQLt.FakeTable 'Album';
    EXEC tSQLt.SpyProcedure 'LogError'
    
    EXEC tsqlt.ExpectException;
    EXEC AddNewAlbum 'Showaddywaddy', 'Trocadero';
    
    SELECT 'Who put the bomp In the bomp bah bomp bah bomp?' as message into Expected;
    SELECT message into Actual from LogError_SpyProcedureLog ;
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual'; 
        
    But so does this one!

    Expecting Exceptions

    
    CREATE PROCEDURE AddNewAlbum.[Test missing artist throws exception and writes log]
    AS
    EXEC tSQLT.FakeTable 'Artist';
    EXEC tSQLt.FakeTable 'Album';
    EXEC tSQLt.SpyProcedure 'LogError'
    
    EXEC tsqlt.ExpectException;
    EXEC AddNewAlbum 'Showaddywaddy', 'Trocadero';
    
    SELECT 'Who put the bomp In the bomp bah bomp bah bomp?' as message into Expected;
    SELECT message into Actual from LogError_SpyProcedureLog ;
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual'; 
    
    But so does this one!

    Expecting Exceptions

    
    CREATE PROCEDURE AddNewAlbum.[Test missing artist throws exception and writes log]
    AS
    EXEC tSQLT.FakeTable 'Artist';
    EXEC tSQLt.FakeTable 'Album';
    EXEC tSQLt.SpyProcedure 'LogError'
    
    EXEC tsqlt.ExpectException;
    EXEC AddNewAlbum 'Showaddywaddy', 'Trocadero';
    
    SELECT 'Who put the bomp In the bomp bah bomp bah bomp?' as message into Expected;
    SELECT message into Actual from LogError_SpyProcedureLog ;
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual'; 
    
    But so does this one!

    Expecting Exceptions

    
    CREATE PROCEDURE AddNewAlbum.[Test missing artist throws exception and writes log]
    AS
    EXEC tSQLT.FakeTable 'Artist';
    EXEC tSQLt.FakeTable 'Album';
    EXEC tSQLt.SpyProcedure 'LogError'
    
    EXEC tsqlt.ExpectException
    EXEC AddNewAlbum 'Showaddywaddy', 'Trocadero';
    
    SELECT 'Who put the bomp In the bomp bah bomp bah bomp?' as message into Expected;
    SELECT message into Actual from LogError_SpyProcedureLog ;
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual'; 
    	
    But so does this one!

    Expecting Exceptions

    
    CREATE AddNewAlbum.[Test missing artist writes log record]
    AS
    EXEC tSQLT.FakeTable 'Artist';
    EXEC tSQLt.FakeTable 'Album';
    EXEC tSQLt.SpyProcedure 'LogError'
    
    BEGIN TRY
    	EXEC AddNewAlbum 'Showaddywaddy', 'Trocadero';
    END TRY
    BEGIN CATCH
    END CATCH
    SELECT 'The artist name does not exist' as message into Expected;
    SELECT message into Actual from LogError_SpyProcedureLog ;
    
    EXEC tSQLt.AssertEqualsTable 'Expected', 'Actual';
    GO
    
    /*********************************************************/
    CREATE PROCEDURE AddNewAlbum.[Test missing artist throws exception]
    AS
    EXEC tSQLT.FakeTable 'Artist';
    EXEC tSQLt.FakeTable 'Album';
    EXEC tSQLt.SpyProcedure 'LogError'
    
    EXEC tsqlt.ExpectException;
    EXEC AddNewAlbum 'Showaddywaddy', 'Trocadero';
        
    Create a separate test for the logging

    Treat Exceptions as separate tests


    You don't need to test that SQL Server is working

    Summary

    Arrange - Act - Assert

    Mock dependencies to isolate the code under test

    Only test the code you own

    Change the Code, not the Test, to generate a failure

    Observe test naming conventions

    Use data builders rather than setup procs

    Modular code is easier to test

    Inject dependencies where possible

    Be careful with exceptions

    Thanks for listening