Thursday, March 1, 2012

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
  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';
  30:  --select results
  31:  select * from #testresults;
  33:  drop table #testresults;

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

   1:  --===================SAMPLE TEST HELPER: assert=====================
   3:  create procedure dbo.TestUtil_Assert
   4:  @expected nvarchar(250),
   5:  @actual nvarchar(250),
   6:  @errmsg nvarchar(250)
   7:  as
   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))

No comments:

Post a Comment

Let me know what you think?