How to Test SQL Server Functions and Procedures using SQL Prompt

When writing functions or procedures, a common chore is to devise and implement the tests that ensure that the routine always works as expected. The best way to do this is to define the tests in a batch that is attached to the CREATE or ALTER script. This isn’t just to prevent you from making any obvious mistakes, as you develop the routine, but also to prevent anyone else from subsequently altering it when they are ‘feeling lucky’, without doing manual unit tests.

From my experience, it has saved me from embarrassment, and caught out a few colleagues who sought to improve my work, without considering the unpleasant side-effects. It is often a life-saver when working on performance improvements.

These tests can take time to do properly, but there is a very handy feature in SQL Prompt that considerably lightens the workload. Let me explain, by way of an example.

Basic testing for a function to trim leading zeros

Listing 1 is a routine that removes leading zeros from a string.

IF (OBJECT_ID('dbo.TrimLeadingZeros', 'IF') IS NOT NULL)
      DROP FUNCTION dbo.TrimLeadingZeros
  GO
  -- if the routine exists this stub creation stem is parsed but not executed
  CREATE FUNCTION dbo.TrimLeadingZeros
  (
      @String VARCHAR(500)
  )
  /**
            summary:   >
            This function returns a string with all leading zeros removed.
            Author: Phil Factor
            date: 8th Aug 2017
            example:
                - code: select WithoutLeadingZeros from dbo.TrimLeadingZeros('000678')
                - code: select  WithoutLeadingZeros from dbo.TrimLeadingZeros('678')
                - code: Select  WithoutLeadingZeros from dbo.TrimLeadingZeros('000')
            returns:   >
            Input string without leading zeros 
            
            **/
  RETURNS TABLE
  WITH SCHEMABINDING
  AS
  RETURN
  (
      SELECT CASE
                 WHEN LTRIM(@String) NOT LIKE '%[^0]%' THEN
                     '0'
                 ELSE
                     STUFF(
                              ' ' + LTRIM(@String),
                              1,
                              PATINDEX('%[^0]%', '0' + @String + '!' COLLATE SQL_Latin1_General_CP850_BIN) - 1,
                              ''
                          )
             END AS WithoutLeadingZeros
  )
  GO

Listing 1

You now have the problem of testing it. It is a good practice to attach a batch of tests to the build script, to catch all obvious mistakes. Generally, you will want to pass in to the routine parameters that contain known values, and examine the result to ensure that the routine does what is expected and doesn’t do anything unexpected.

Listing 2, appended directly to the end of Listing 1, will run a few tests, checking for various obvious problems, every time we create or alter the object.

IF EXISTS
  (
      SELECT *
      FROM
      (
          VALUES
              (' 00001', '1'),
              ('00000', '0'),
              ('0296009', '296009'),
              ('0120000', '120000'),
              ('3218', '3218'),
              ('0-002', '-002') --
      ) AS testCases (TheInput, TheOutput)
          OUTER APPLY TrimLeadingZeros(testCases.TheInput) AS TLZ
      WHERE TheOutput <> WithoutLeadingZeros
  )
      RAISERROR('TrimLeadingZeros failed a test', 16, 1)

Listing 2

What I’ve done is to create a table-source, using a multi-row VALUES statement. This gives me known input. The second string in the pair is the expected output. All I need to do is to check the expected output with what was actually output, and raise an error if there was a difference. This is done to alert the build process that a basic test is failed.

Building up the test harness using SQL Prompt

I haven’t really got enough tests to build confidence. I need at least fifty, including some created with random data, so I start to build a table of ‘before’ and ‘after’ values.

SELECT f.RandomNumberWithLeadingZeros,
         WithoutLeadingZeros
  FROM
  (
      SELECT LEFT('00000000', CONVERT(INT, RAND() * 6)) + CONVERT(VARCHAR(10), CONVERT(INT, RAND() * 1000000))
      UNION ALL
      SELECT LEFT('00000000', CONVERT(INT, RAND() * 6)) + CONVERT(VARCHAR(10), CONVERT(INT, RAND() * 1000000))
  ) f(RandomNumberWithLeadingZeros)
      OUTER APPLY TrimLeadingZeros(RandomNumberWithLeadingZeros)

Listing 3

Which gives something like this…

Doesn’t look much does it? Now, with the power of cut ‘n paste, a few seconds later we have fifty rows, which should be sufficient.


Specify grid output, execute the newly-fattened query, and then select the entire grid by clicking on the top left square.

We now right-click on the grid, and discover a SQL Prompt hidden treasure, Script as INSERT:

This feature converts this lot into a multi-row VALUES expression. After a bit of manual formatting – I used a regex: search for )\s,\s\r\n, which is a close bracket, any number of spaces, a comma, any number of spaces, followed by a windows line end, and replaced it with ), – it looks like this:

So now we have a test set of over fifty tests, which we add to all the obvious edge cases.

  IF EXISTS
      (SELECT * FROM
         (VALUES
            (' 000001', '1'),('00000', '0'),('0296009', '296009'),('0120000', '120000'),
            ('3218', '3218'),('0-002', '-002'),('000385600', '385600'),('0000767245', '767245'),
            ('0666204', '666204'),('00000289590', '289590'),('000224373', '224373'),
            ('000484384', '484384'),('0000434125', '434125'),('297677', '297677'),
            ('891268', '891268'),('0401720', '401720'),('0564944', '564944'),
             ('00711912', '711912'),('378227', '378227'),('000688843', '688843'),
             ('0000993162', '993162'),('0224156', '224156'),('0620955', '620955'),
             ('0788726', '788726'),('00768133', '768133'),('740', '740'),('990537', '990537'),
            ('00000557330', '557330'),('0285890', '285890'),('000148858', '148858'),
             ('0412430', '412430'),('0000128573', '128573'),('00000602639', '602639'),
             ('0000032732', '32732'),('659634', '659634'),('000793696', '793696'),
             ('0000522385', '522385'),('00578316', '578316'),('464596', '464596'),
            ('00070748', '70748'),('00821231', '821231'),('00835132', '835132'),
             ('000388337', '388337'),('000141630', '141630'),('0000424445', '424445'),
             ('00000541940', '541940'),('00000988638', '988638'),('0000620767', '620767'),
             ('00124705', '124705'),('0361715', '361715'),('0623555', '623555'),
             ('000884301', '884301'),('0000058847', '58847'),('00829404', '829404'),
             ('00000120452', '120452' ),( '000396445', '396445' ) 
           ) AS testCases(TheInput, TheOutput)
           OUTER APPLY TrimLeadingZeros(testCases.TheInput) AS TLZ
         WHERE TheOutput <> WithoutLeadingZeros
      )
      RAISERROR('TrimLeadingZeros failed a test', 16, 1)

Listing 4

The next stage is to go through these tests in detail, to be certain that they are correct. You might need to involve a business stakeholder and test engineer to agree that the output is correct for each input (it is amazing what sort of bugs can emerge from this process!)

Once you’re sure, we can run the tests every time we create or alter the function. If you are doing TDD, you will start with listing 2 which has only the essential requirements, and expand the test once you have a function that passes all these basic tests. You need to keep adding edge cases to be absolutely sure that the function is robust.

Summary

Building this sort of test harness for each object would, at first glance, seem like a lot of extra work. It really isn’t because manual testing takes so much time that it is far better to create a good test harness before you get too far into the work of creating a procedure or function. SQL Prompt is wonderful for creating the data for this type of test harness through being able to generate those table-sources using multi-row VALUES statements.