Get your 'learn' on in 2013

Continuous personal development is an important part of most chartered professions - Doctors, Dentists, Architects, Engineers, etc., and I can't help thinking it's a good thing - the state of knowledge within most areas is constantly expanding and the more knowledge your Doctor has, the better treatment you can get. It's a good thing that Architects can learn new things - it means that we get better buildings.

The rate that the IT field is changing is also rapid. This means that we have to continuously improve our knowledge in order simply to stand still. It's something that some employers will help with, particularly if the topic is relevant to upcoming work, but if your employer doesn't feel able to pick up all of the cost, there are lots of ways to help persuade them that it's cheaper than they think - and your showing an interest will probably tip the balance; it could also help them perceive you as a more enthusiastic and valuable employee.

However, not all conferences need cost you lots of money, if you can get yourself there and make the time.

As you may know, I am a regular attendee at the SQL South West User Group, and the group organisers, FatherJack and Mrs_Fatherjack are putting on the latest SQL Saturday event in Exeter (UK) on the 8th and 9th of March. The Friday is a paid day of deep-dive sessions (with a discount of 19% if you register by 31st January) and Saturday the 9th is a day of 1 hour long sessions on a variety of topics. These are usually long enough to cover a subject, without being too detailed. That means they're suitable for a variety of levels of knowledge.

The SQL Bits team are again putting on a conference on the first weekend in May (a bank holiday weekend), this time in Nottingham. I've written about my experiences attending an earlier SQL Bits conference and I'd highly recommend it.

If you can't free up the weekend of a conference, your local user group can be a gentler introduction, and you will find a list of UK SQL learning opportunities here.You can also see recordings of past sessions from most conferences online at your convenience, but of course that does mean you miss the networking opportunities that attendance at the event brings.

If you've been to conferences in the past, why not contribute back to the community, and submit a session for a conference, or speak at a user group? SQL Saturday 194 is accepting sessions until Monday 8th January 2013, and SQL Bits session submission is also open. Both events welcome submissions from new speakers.

Wherever you are in your career, I hope that you embrace the challenge of learning something new this year.

Testing variable messages just got easier in tSQLt

I recently noticed that the open source unit testing framework I use (and have previously written about) - tSQLt - has had a new release, and with it a new type of assert. Asserts are how you test whether the result you got in a test was what you wanted, and hence whether the test should pass or fail.

This new assert routine, AssertLike, solves one of the more common situations which I've had to work around - that where you know roughly the message you are checking for, but not the totality of it. Let's take the example of a stored procedure which gets all addresses for a given state ID. This is somewhat contrived - there are reasons why you wouldn't do exactly this in a real application, but it makes for an easy to follow example. I'm using the AdventureWorks 2012 database.

/*
   Version       :  1.0
   Written on    :  26/12/2012
   Written by    :  Dave Green
   Purpose of SP :  To retrieve all address records in a given state.
*/
CREATE PROCEDURE dbo.usp_GetAddressesInState
@StateProvinceCode VARCHAR(100)
AS

DECLARE @StateProvinceID INT, @ErrorMessage VARCHAR(100)
--Attempt to get province ID from StateProvince table
SELECT @StateProvinceID = StateProvinceID 
 FROM Person.StateProvince 
 WHERE StateProvinceCode = @StateProvinceCode


IF @StateProvinceID IS NULL --If the stateprovince wasn't valid, raise an error
BEGIN
   SET @ErrorMessage = 'Unable to get StateProvinceID'
   RAISERROR (@ErrorMessage,16,1)
END
--Return customer records
SELECT  AddressID ,
        AddressLine1 ,   AddressLine2 ,
        City ,    StateProvinceID ,
        PostalCode ,  SpatialLocation ,
        ModifiedDate
FROM Person.Address WHERE StateProvinceID = @StateProvinceID
GO

This Stored Proc checks that the Province ID exists before it returns the results; this means that no results mean that there are no results, rather than an invalid (or not on record) state. This sort of distinction is quite common for applications where you want to return a more helpful message to the user to allow them to take action.

We can test this SP using the following tSQLt code:

/*
   Version       :  1.0
   Written on    :  26/12/2012
   Written by    :  Dave Green
   Purpose of Class :  Demo of AssertLike
*/
EXEC tSQLt.NewTestClass 'ALDemo'

GO

/*
   Version    :  1.0
   Written on :  26/12/2012
   Written by :  Dave Green
   Purpose    :  check that invalid States give an error
*/
CREATE PROCEDURE ALDemo.[test invalid State code produces error]
AS
/**********************************************
**** To verify that the SP throws an error 
**** when fed with an invalid state code
***********************************************/
--Assemble
--Act
  --  Execute the code under test
BEGIN TRY

 EXEC dbo.usp_GetAddressesInState @StateProvinceCode = 'test' -- varchar(100)

   --Assert
 EXEC tSQLt.Fail 'Expected error was not thrown'
END TRY
          
BEGIN CATCH
 DECLARE @Actual NVARCHAR(MAX) = ERROR_MESSAGE()
 EXEC tSQLt.AssertEqualsString @Expected = N'Unable to get StateProvinceID', -- nvarchar(max)
    @Actual = @Actual, -- nvarchar(max)
    @Message = 'The error thrown was different to that which was expected' -- nvarchar(max)

END CATCH

GO
EXEC tSQLt.Run 'ALDemo' -- Run the test

This works successfully, and tests the proc, checking that the correct error message is thrown (i.e. there isn't another error which is causing the proc to fail first).

This is all well and good, but then a requirement for change comes that the error message shown to the caller should specify the erroneous value. This is easily accomplished in the SP:

/*
   Written on :  26/12/2012
   Written by :  Dave Green
   Purpose    :  Get address records for a given state.
*/

/* Updated by Dave Green 26/12/2012 to return any erroneous state in the error message.*/

ALTER PROCEDURE usp_GetAddressesInState
@StateProvinceCode VARCHAR(100)
AS

DECLARE @StateProvinceID INT, @ErrorMessage VARCHAR(100)
--Attempt to get province ID from StateProvince table
SELECT @StateProvinceID = StateProvinceID 
FROM Person.StateProvince 
WHERE StateProvinceCode = @StateProvinceCode

IF @StateProvinceID IS NULL --If the stateprovince wasn't valid, raise an error
BEGIN
   SET @ErrorMessage = 'Unable to get StateProvinceID - Province '
                       +@StateProvinceCode+' may be invalid.'
   RAISERROR (@ErrorMessage,16,1)
END --Return customer records
SELECT  AddressID ,
        AddressLine1 ,   AddressLine2 ,
        City ,    StateProvinceID ,
        PostalCode ,  SpatialLocation ,
        ModifiedDate
FROM Person.Address WHERE StateProvinceID = @StateProvinceID
GO

However, how can we adjust the unit test to accommodate this? The error message will change each time the SP is called - to reflect the input parameter.

The answer has always been to put in place custom logic in the 'CATCH' block - 'If error message is like this, then fine, else fail'.
This is realised in code as:

BEGIN CATCH
   DECLARE @Actual NVARCHAR(MAX) = ERROR_MESSAGE()
   IF @Actual IS NULL OR @Actual NOT LIKE 'Unable to get StateProvinceID - Province % may be invalid.'
   EXEC tSQLt.Fail @Message0 = 'The error thrown was different to that which was expected' 
END CATCH

Of course, if you are checking a specific value, or date/time, this gets more complicated. A line number in the error message makes life even more unpredictable. This is in my view a little ugly, and doesn't lead itself to easy reading in the same way that the simple assert did. The good news is that the latest version of tSQLt now has an AssertLike assert procedure, so we can now simplify this as:

BEGIN CATCH
 DECLARE @Actual NVARCHAR(MAX) = ERROR_MESSAGE()
 EXEC tSQLt.AssertLike @ExpectedPattern = N'Unable to get StateProvinceID - Province % may be invalid.', -- nvarchar(max)
     @Actual = @Actual, -- nvarchar(max)
     @Message = N'The error thrown was different to that which was expected' -- nvarchar(max)
END CATCH

The new assert gives us a standardised approach which also includes a null check (on both sides). This allows a neater approach than custom logic for this basic type of check, and has particular applicability where the line number of an error is reported back as part of the message. This is because tests would then not break solely for a comment being added to the procedure, thus reducing the work required to 'fix' such tests when such a comment is added.

This seems to me to be much neater, and should be more a maintainable test for the future too, as it follows the same form as asserts in other tests.