Running Unit Tests in database

Well, you may be thinking about how to test behavior of the stored procedures for different inputs. You may want to be sure that your procs are doing what they are supposed to do. They are giving correct access or sending correct payment amount. You can test those from your app as well, but independent database tests are better in terms of flexibility to run them without apps.

I have recently setup one build automation and decided to re-create database from scratch with seed data to test everything. Of course, it takes time to write tests for app and database, but it is a good start.

Some quick tips to test your stored procs:

You need to call only one proc to run all of your database tests. It can insert results into temp table and just return that.

Example unit test call:

   1:  --======================TESTs======================

   2:  IF OBJECT_ID('tempdb..#testresults') IS NOT NULL

   3:  BEGIN

   4:  PRINT '#temp exists!'

   5:  --drop

   6:  drop table #testresults;

   7:  END

   8:  ELSE

   9:  BEGIN

  10:  PRINT '#temp does not exist!'

  11:  END

  12:   

  13:   

  14:  Create table #testresults

  15:  (resultid int identity(1,1) primary key,

  16:  [Status] nvarchar(50) not null,

  17:  Expected nvarchar(250) null,

  18:  Actual nvarchar(250) null,

  19:  ErrMsg nvarchar(250) null

  20:  )

  21:  --======================TEST 1======================

  22:  declare @accesCheck bit

  23:  EXEC Proc_Exp_HasUserAccessToFunction @username,'testfunc1',@accesCheck output;

  24:  EXEC TestUtil_Assert 1,@accesCheck,'testfunc1 access is not right';

  25:  --======================TEST 2======================

  26:  EXEC Proc_Exp_HasUserAccessToFunction @username,'testfunc2',@accesCheck output;

  27:  EXEC TestUtil_Assert 1,@accesCheck,'testfunc2 access is not right';

  28:   

  29:   

  30:  --select results

  31:  select * from #testresults;

  32:   

  33:  drop table #testresults;

You can create Assert statements at database side as well. Here is Assert.areEqual call:

   1:  --===================SAMPLE TEST HELPER: assert=====================

   2:   

   3:  create procedure dbo.TestUtil_Assert

   4:  @expected nvarchar(250),

   5:  @actual nvarchar(250),

   6:  @errmsg nvarchar(250)

   7:  as

   8:   

   9:    if (@expected is null and @actual is null) or  (@expected = @actual)

  10:        insert into #testresults

  11:        select 'PASS' as Status,cast( @expected as nvarchar(250)),cast(@actual as nvarchar(250)), cast( @errmsg as nvarchar(250))

  12:    ELSE

  13:      insert into #testresults

  14:      select 'FAILURE' as Status,cast( @expected as nvarchar(250)),cast(@actual as nvarchar(250)), cast( @errmsg as nvarchar(250))