As we develop applications, it often makes sense to put some functions in a SQL CLR assembly. As these assemblies are deployed through test, beta and production environments we need an easy way to ensure the assemblies have been updated correctly and are functioning as designed. Many things can go wrong—incorrect permissions, deploying the wrong version of an assembly, even assumptions we made in the business logic. I’m a big fan of RedGate’s SQL Test plugin—it’s an attractive SSMS plugin which organizes and runs tests via an easy to read panel—and the tSQLt (a unit testing framework for SQL Serve) which SQL Test is based on. My next post will show recreate the testing strategy shown here using SQL Test.
One downside to tSQt is that you have to install a number of stored procedures and functions into your database. Depending on your choice of database, permissions in your database and willingness to add additional objects to your database which aren’t drectly related to the data, using tSQLt may not be an option. Although we’re testing a user defined function, we could also test stored procedures and SQLCLR assemblies. This technique is also not limited to SQL Server—I use this in both SQL Server and VistaDB. As you build out your application, you may want to consider adding diagnostic pages (or screens) where you can execute these testing procedures should your application start throwing errors.
The function we’ll be testing will be simple—we’ll pass in an nvarchar, and return the same nvarchar with the word “wombat†added to the end.
Start by creating a custom function:
CREATE FUNCTION [AddWombat] ( @phrase NVarChar(50) ) RETURNS NVarChar AS BEGIN return @phrase + ' wombat' END
Now it’s time for the test harness. I’m using VistaDB, creating a stored procedure and using table variables, syntax supported by more recent versions of SQL Server. You may need to optimize for the database you’re using. The first table holds our test cases—the test name, the value we want to pass into our function, and the expected result. We then loop through all the tests, calling the function we want to test, and updating the result. Our result comparison is very simple, similar to an Assert.AreEqual. With a little more work, additional test types could be added, and the test indicated in the test case.
create procedure TestAddWombat as begin DECLARE @testcases TABLE(TestNumber INT IDENTITY(1,1) PRIMARY KEY, TestName NVARCHAR(100), TestCase NVARCHAR(100), Expected NVARCHAR(100), Result NVARCHAR(20)); DECLARE @testname VARCHAR(100); declare @testcase nvarchar(MAX); DECLARE @expected NVARCHAR(MAX); DECLARE @result NVARCHAR(max); DECLARE @testCaseCount INT; DECLARE @counter INT; SET @counter = 1; INSERT INTO @testcases ( TestName , TestCase , Expected ) VALUES ( N'should_return_hello_wombat' , -- TestName - nvarchar(100) N'Hello, ' , -- TestCase - nvarchar(100) N'Hello, wombat' -- Expected - nvarchar(100) ) INSERT INTO @testcases ( TestName , TestCase , Expected ) VALUES ( N'should_return_null' , -- TestName - nvarchar(100) NULL , -- TestCase - nvarchar(100) NULL -- Expected - nvarchar(100) ) SELECT @testCaseCount = count(*) FROM @testcases WHILE @counter <= @testCaseCount BEGIN SELECT @testname = TestName, @expected = Expected, @testcase = TestCase FROM @testcases WHERE TestNumber = @counter SELECT @result = dbo.AddWombat(@testcase) IF(@expected is NULL) BEGIN IF(@result is NULL) UPDATE @testcases SET Result = 'True' WHERE TestNumber = @counter ELSE UPDATE @testcases SET Result = 'False' WHERE TestNumber = @counter END ELSE BEGIN IF(@expected = @result) UPDATE @testcases SET Result = 'True' WHERE TestNumber = @counter ELSE UPDATE @testcases SET Result = 'False' WHERE TestNumber = @counter END SET @counter = @counter + 1; END SELECT * FROM @testcases end
Running this sproc returns the following results:
From here, we have a very basic test harness which we can easily add additional test cases, and easily extend. TDD is a best practice in any aspect of application development, but database development is often not treated as application development and is far behind adopting practices common to C# developers. This script is a simple way to introduce some TDD into database development.
