CODEWITHSUNDEEP
sql-serverfunctionunit-testingstored-procedures
Tarta
Tarta

Reputation: 2063

Unit testing stored procedure and functions in .NET Core and SQL Server

Problem: how to unit test stored procedure and function?

I have seen different posts about it and still don't know what is the right way.

What I tried: I created a project in Visual Studio to upload the database schema (containing my stored procedure and related function). From there I create another project with a unit test. I unfortunately however find it trivial and a bit useless.

My stored procedure is not big deal but I would still like to know if there is a way to unit test it properly.

Stored procedure:

CREATE PROCEDURE update_player 
    @Username   NVARCHAR (250),
    @Luck       INT,
    @DiceNumber INT
AS
    UPDATE [dbo].[Player]
    SET [Coins] = dbo.CalculatePlayerCoins(@Luck, @DiceNumber)
    WHERE [Username] = @Username 
 GO

My function:

CREATE FUNCTION CalculatePlayerCoins 
    (@Luck INT, @DiceNumber INT)  
RETURNS INT
AS    
BEGIN  
    DECLARE @ret INT;

    SELECT 
        @ret = @Luck * @DiceNumber * (SELECT Value FROM getRandomValue)

    IF (@ret IS NULL)   
        SET @ret = 0;  

    RETURN @ret;  
END;

getRandomValue is a view that returns a random value.

I would like to use xUnit to test this if possible. Otherwise if you could point me to the right direction would be awesome. Thanks in advance!

Upvotes: 0

Views: 1514

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

Views are usually are based on tables which can be seeded with known values for unit testing. In this case, you'll need to temporarily replace the view that returns random values (presumably using RAND() or similar) with a stub that returns known values for testing.

Below is an example using the tsqlt unit test framework. The tSQLt.Run framework proc executes unit tests in a transaction, which is rolled back after the test. Although unit tests like this can be executed against a shared development database, I recommend running unit tests on an isolated database to avoid impacting other developers.

CREATE PROC dbo.test_dbo_update_player
AS

--create a stub view that returns known values for unit testing
EXEC sp_rename N'dbo.getRandomValue', 'getRandomValue_orig';
EXEC('CREATE VIEW dbo.getRandomValue AS SELECT 1 AS value;');

--create a fake table with known data for this test case
EXEC tSQLt.FakeTable 'dbo.Player';
INSERT INTO dbo.Player (UserName,Coins) Values(N'Test', 0);

EXEC dbo.update_player 
      @Username   = N'Test'
    , @Luck       = 2
    , @DiceNumber = 3;

DECLARE @actual int =(SELECT Coins FROM dbo.Player WHERE UserName = N'Test');
EXEC tSQLt.AssertEquals @expected = 6, @actual = @actual, @message = 'assert equal failed for test case';
GO

--unit test framework executes unit test in a transaction and will rollback after completion
EXEC tSQLt.Run @testName = 'dbo.test_dbo_update_player';
GO

Upvotes: 2

Related Questions