In Transact SQL you can assign a 10-character value to a
VARCHAR(5) variable, as follows:
DECLARE @c VARCHAR(5); SET @c='1234567890'; PRINT @c
12345
The value will be silently truncated without raising an
error. Because of this behavior it may be very easy to make mistakes. For
example, consider the following table and stored procedure:
CREATE TABLE Data.Codes(
Code VARCHAR(5),
Description VARCHAR(40)
);
GO
CREATE PROCEDURE Readers.SelectCodes
@Code VARCHAR(5)
AS
BEGIN
SELECT
Description FROM Data.Codes
WHERE Code =
@Code
END
GO
Although the procedure works correctly, it is very easy to
render it incorrect by changing the table it selects from. Suppose that you have
to increase the width of Code column, as follows:
ALTER TABLE Data.Codes ALTER COLUMN Code VARCHAR(10);
GO
If you have not adjusted the width of the
corresponding parameter
accordingly, then you have just introduced a new bug into your system.
The procedure would fail to retrieve some rows. You can see for
yourself:
INSERT INTO Data.Codes VALUES('1234567890', 'Tets data for boundary case');
EXEC Readers.SelectCodes @Code = '1234567890';
It easy to demonstrate that the procedure has failed to retrieve because your parameter has been silently
truncated to 12345:
ALTER PROCEDURE Readers.SelectCodes
@Code VARCHAR(5)
AS
BEGIN
SELECT @Code AS PassedValue
SELECT
Description FROM Data.Codes
WHERE Code =
@Code
END
GO
EXEC Readers.SelectCodes @Code = '1234567890'
12345
There are several ways to limit your exposure to such
problems. For instance, Oracle’s PL/SQL has built in %TYPE syntax which
guarantees that the parameter’s type matches the type of the corresponding
column, and apparently that feature is very popular with Oracle developers.
Alternatively, some developers create their own types, but this
approach seems to be unpopular.
Also there are a couple of suggestions on Connect requesting similar functionality
in SQL Server.
Also to avoid such problems, SQL Server MVP Simon Sabin recommends using LINQ.
Anyway, if you want to improve the
robustness of an existing system without a major overhaul, then you are stuck
with your VARCHAR columns, because neither of these approaches would work for
your existing tables and procedures.
This is one of those cases where boundary values testing
proves to be very useful. Boundary values tests are supposed to verify that the
procedure handles all the extreme cases, including the case when the code is of
maximum allowed width. At the time when you are developing your stored
procedure, create a boundary value test, as follows:
[Test]
private
void SelectCodesMaxWidthTest()
{
string
maxWidthCode = "12345";
//verify
that maxWidthCode is indeed a boundary value
Assert.AreEqual(maxWidthCode.Length,
GetColumnWidth("Data", "Codes", "Code"));
//execute
the procedure and check its results
That done, when you change the Code column’s width, the test
will fail and remind you to change the procedure too.
Next post in the series:
Defensive
database programming: eliminating IF statements.