Developing a SQL Server Function in a Test-Harness.
- by Phil Factor
/*
Many times, it is a lot quicker to take some pain up-front and make a proper development/test harness for a routine (function or procedure) rather than think ‘I’m feeling lucky today!’. Then, you keep code and harness together from then on. Every time you run the build script, it runs the test harness too. The advantage is that, if the test harness persists, then it is much less likely that someone, probably ‘you-in-the-future’ unintentionally breaks the code. If you store the actual code for the procedure as well as the test harness, then it is likely that any bugs in functionality will break the build rather than to introduce subtle bugs later on that could even slip through testing and get into production.
This is just an example of what I mean.
Imagine we had a database that was storing addresses with embedded UK postcodes. We really wouldn’t want that. Instead, we might want the postcode in one column and the address in another. In effect, we’d want to extract the entire postcode string and place it in another column. This might be part of a table refactoring or int could easily be part of a process of importing addresses from another system.
We could easily decide to do this with a function that takes in a table as its parameter, and produces a table as its output. This is all very well, but we’d need to work on it, and test it when you make an alteration. By its very nature, a routine like this either works very well or horribly, but there is every chance that you might introduce subtle errors by fidding with it, and if young Thomas, the rather cocky developer who has just joined touches it, it is bound to break.
right, we drop the function we’re developing and re-create it. This is so we
avoid the problem of having to change CREATE to ALTER when working on it. */
IF EXISTS(SELECT * FROM sys.objects WHERE name LIKE ‘ExtractPostcode’
and schema_name(schema_ID)=‘Dbo’)
DROP FUNCTION dbo.ExtractPostcode
GO
/* we drop the user-defined table type and recreate it */
IF EXISTS(SELECT * FROM sys.types WHERE name LIKE ‘AddressesWithPostCodes’
and schema_name(schema_ID)=‘Dbo’)
DROP TYPE dbo.AddressesWithPostCodes
GO
/* we drop the user defined table type and recreate it */
IF EXISTS(SELECT * FROM sys.types WHERE name LIKE ‘OutputFormat’
and schema_name(schema_ID)=‘Dbo’)
DROP TYPE dbo.OutputFormat
GO
/* and now create the table type that we can use to pass the addresses to the function */
CREATE TYPE AddressesWithPostCodes AS TABLE
(
AddressWithPostcode_ID INT IDENTITY PRIMARY KEY, –because they work better that way!
Address_ID INT NOT NULL, –the address we are fixing
TheAddress VARCHAR(100) NOT NULL –The actual address
)
GO
CREATE TYPE OutputFormat AS TABLE
(
Address_ID INT PRIMARY KEY, –the address we are fixing
TheAddress VARCHAR(1000) NULL, –The actual address
ThePostCode VARCHAR(105) NOT NULL – The Postcode
)
GO
CREATE FUNCTION ExtractPostcode(@AddressesWithPostCodes AddressesWithPostCodes READONLY)
/**
summary: >
This Table-valued function takes a table type as a parameter, containing a table of addresses along with their integer IDs. Each address has an embedded postcode somewhere in it but not consistently in a particular place. The routine takes out the postcode and puts it in its own column, passing back a table where theinteger key is accompanied by the address without the (first) postcode and the postcode. If no postcode, then the address is returned unchanged and the postcode will be a blank string
Author: Phil Factor
Revision: 1.3
date: 20 May 2014
example:
– code:
returns: >
Table of Address_ID, TheAddress and ThePostCode.
**/
RETURNS @FixedAddresses TABLE
(
Address_ID INT, –the address we are fixing
TheAddress VARCHAR(1000) NULL, –The actual address
ThePostCode VARCHAR(105) NOT NULL – The Postcode
)
AS
– body of the function
BEGIN
DECLARE @BlankRange VARCHAR(10)
SELECT @BlankRange = CHAR(0)+‘- ‘+CHAR(160)
INSERT INTO @FixedAddresses(Address_ID, TheAddress, ThePostCode)
SELECT Address_ID,
CASE WHEN start>0 THEN REPLACE(STUFF([Theaddress],start,matchlength,”),‘ ‘,‘ ‘)
ELSE TheAddress END
AS TheAddress,
CASE WHEN Start>0 THEN SUBSTRING([Theaddress],start,matchlength-1) ELSE ” END AS ThePostCode
FROM
(–we have a derived table with the results we need for the chopping
SELECT MAX(PATINDEX([matched],‘ ‘+[Theaddress] collate SQL_Latin1_General_CP850_Bin)) AS start,
MAX( CASE WHEN PATINDEX([matched],‘ ‘+[Theaddress] collate SQL_Latin1_General_CP850_Bin)>0 THEN TheLength ELSE 0 END) AS matchlength,
MAX(TheAddress) AS TheAddress,
Address_ID
FROM (SELECT –first the match, then the length. There are three possible valid matches
‘%['+@BlankRange+'][A-Z][0-9] [0-9][A-Z][A-Z]%’, 7 –seven character postcode
UNION ALL SELECT ‘%['+@BlankRange+'][A-Z][A-Z0-9][A-Z0-9] [0-9][A-Z][A-Z]%’, 8
UNION ALL SELECT ‘%['+@BlankRange+'][A-Z][A-Z][A-Z0-9][A-Z0-9] [0-9][A-Z][A-Z]%’, 9)
AS f(Matched,TheLength)
CROSS JOIN @AddressesWithPostCodes
GROUP BY [address_ID]
) WORK;
RETURN
END
GO
——————————-end of the function————————
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name LIKE ‘ExtractPostcode’)
BEGIN
RAISERROR (‘There was an error creating the function.’,16,1)
RETURN
END
/* now the job is only half done because we need to make sure that it works.
So we now load our sample data, making sure that for each Sample, we have what we actually think the output should be. */
DECLARE @InputTable AddressesWithPostCodes
INSERT INTO @InputTable(Address_ID,TheAddress)
VALUES(1,’14 Mason mews, Awkward Hill, Bibury, Cirencester, GL7 5NH’),
(2,’5 Binney St Abbey Ward Buckinghamshire HP11 2AX UK’),
(3,‘BH6 3BE 8 Moor street, East Southbourne and Tuckton W Bournemouth UK’),
(4,’505 Exeter Rd, DN36 5RP Hawerby cum BeesbyLincolnshire UK’),
(5,”),
(6,’9472 Lind St, Desborough Northamptonshire NN14 2GH NN14 3GH UK’),
(7,’7457 Cowl St, #70 Bargate Ward Southampton SO14 3TY UK’),
(8,”’The Pippins”, 20 Gloucester Pl, Chirton Ward, Tyne & Wear NE29 7AD UK’),
(9,’929 Augustine lane, Staple Hill Ward South Gloucestershire BS16 4LL UK’),
(10,’45 Bradfield road, Parwich Derbyshire DE6 1QN UK’),
(11,’63A Northampton St, Wilmington Kent DA2 7PP UK’),
(12,’5 Hygeia avenue, Loundsley Green WardDerbyshire S40 4LY UK’),
(13,’2150 Morley St,Dee Ward Dumfries and Galloway DG8 7DE UK’),
(14,’24 Bolton St, Broxburn, Uphall and Winchburg West Lothian EH52 5TL UK’),
(15,’4 Forrest St, Weston-Super-Mare North Somerset BS23 3HG UK’),
(16,’89 Noon St, Carbrooke Norfolk IP25 6JQ UK’),
(17,’99 Guthrie St, New Milton Hampshire BH25 5DF UK’),
(18,’7 Richmond St, Parkham Devon EX39 5DJ UK’),
(19,’9165 laburnum St, Darnall Ward Yorkshire, South S4 7WN UK’)
Declare @OutputTable OutputFormat –the table of what we think the correct results should be
Declare @IncorrectRows OutputFormat –done for error reporting
–here is the table of what we think the output should be, along with a few edge cases.
INSERT INTO @OutputTable(Address_ID,TheAddress, ThePostcode)
VALUES
(1, ’14 Mason mews, Awkward Hill, Bibury, Cirencester, ‘,‘GL7 5NH’),
(2, ’5 Binney St Abbey Ward Buckinghamshire UK’,‘HP11 2AX’),
(3, ’8 Moor street, East Southbourne and Tuckton W Bournemouth UK’,‘BH6 3BE’),
(4, ’505 Exeter Rd,Hawerby cum Beesby Lincolnshire UK’,‘DN36 5RP’),
(5, ”,”),
(6, ’9472 Lind St,Desborough Northamptonshire NN14 3GH UK’,‘NN14 2GH’),
(7, ’7457 Cowl St, #70 Bargate Ward Southampton UK’,‘SO14 3TY’),
(8, ”’The Pippins”, 20 Gloucester Pl, Chirton Ward,Tyne & Wear UK’,‘NE29 7AD’),
(9, ’929 Augustine lane, Staple Hill Ward South Gloucestershire UK’,‘BS16 4LL’),
(10, ’45 Bradfield road, ParwichDerbyshire UK’,‘DE6 1QN’),
(11, ’63A Northampton St,Wilmington Kent UK’,‘DA2 7PP’),
(12, ’5 Hygeia avenue, Loundsley Green WardDerbyshire UK’,‘S40 4LY’),
(13, ’2150 Morley St, Dee Ward Dumfries and Galloway UK’,‘DG8 7DE’),
(14, ’24 Bolton St,Broxburn, Uphall and Winchburg West Lothian UK’,‘EH52 5TL’),
(15, ’4 Forrest St,Weston-Super-Mare North Somerset UK’,‘BS23 3HG’),
(16, ’89 Noon St, Carbrooke Norfolk UK’,‘IP25 6JQ’),
(17, ’99 Guthrie St, New Milton Hampshire UK’,‘BH25 5DF’),
(18, ’7 Richmond St, Parkham Devon UK’,‘EX39 5DJ’),
(19, ’9165 laburnum St, Darnall Ward Yorkshire, South UK’,‘S4 7WN’)
insert into @IncorrectRows(Address_ID,TheAddress, ThePostcode)
SELECT Address_ID,TheAddress,ThePostCode FROM dbo.ExtractPostcode(@InputTable)
EXCEPT
SELECT Address_ID,TheAddress,ThePostCode FROM @outputTable;
If @@RowCount>0
Begin
PRINT ‘The following rows gave ‘;
SELECT Address_ID,TheAddress,ThePostCode FROM @IncorrectRows
RAISERROR (‘These rows gave unexpected results.’,16,1);
end
/* For tear-down, we drop the user defined table type */
IF EXISTS(SELECT * FROM sys.types WHERE name LIKE ‘OutputFormat’
and schema_name(schema_ID)=‘Dbo’)
DROP TYPE dbo.OutputFormat
GO
/* once this is working, the development work turns from a chore into a delight and one ends up hitting execute so much more often to catch mistakes as soon as possible. It also prevents a wildly-broken routine getting into a build! */
