Last week I attended another ILP workshop, this time about nUnit. This made me remember about the TSQLUnit testing framework, and the changes I had made to it whilst using it at my previous company and the fact that these changes were just sitting in a file somewhere and had never been shared.

The project which benefited from the most from TSQLUnit was one working with a large set of complicated calculations. Due to the nature of the data, this work was carried out in the database layer. These calculations were based on a well specified system, and so was a great candidate for thorough unit testing. Whilst building the calculation engine, I enhanced the base TSQLUnit installation to have some additional helper functions to save me coding the same thing over and over again.

I added a set of value checking helper procedures as follows
tsu_CheckValues_Decimal, tsu_CheckValues_Int, tsu_CheckValues_String, tsu_CheckValues_Date, tsu_CheckValues_Money all of which take 3 parameters - the first the expected data value, the second the actual data value, and the third an optional parameter expressing the message to be displayed to the developer if this assertion fails.

For example, EXEC tsu_CheckValues_Int 0,1,'Oops'
results in the following error being recorded 'Integer value mismatch Oops - Unexpected Result. Expected: 0. Actual: 1'

In additon I added some helper functions to assist in the generation of error messages as follows
tsu_GenerateErrorMessage_Date, tsu_GenerateErrorMessage_Int, tsu_GenerateErrorMessage_Real, tsu_GenerateErrorMessage_String all of which take 3 parameters, the first being the initial part of the message to display, the second the expected data value and the final the actual data value.

For example, SELECT dbo.tsu_GenerateErrorMessage_Int('Integer value mismatch',0,1) which results in the following text being generated 'Integer value mismatch - Unexpected Result. Expected: 0. Actual: 1'

The helper functions do not alter or change the basis under which TSQLUnit operates, and so they should install and work happily within existing TSQLUnit installations. Download the TSQLUnit Helper Functions.

I have also provided a script containing sample TSQLUnit tests - one coded to pass, one coded to fail, both making use of the tsu_CheckValues_Int.

In another post I will go into the other changes I made, and the recommendations for testing we came up with as a result of this exercise.

Labels: ilp, SQLServer2000, SQLServer2005, TSQLUnit

// posted by Jane @ 1:57 PM   

Comments:

Post a Comment

Simon popped over earlier to ask if I knew a way to capture the data within a SELECT returned as part of a stored procedure within TSQL. So, the item he was after wasn't an output variable, neither was it the return value. I remembered roughly how to do it, and then hunted through some of our TSQLUnit tests to find an example to send to him. I'd used temporary tables to do it. I wondered why I'd use that instead of a table variable, so I thought I'd write a bit of code to find out. The reason was that we got the error "EXECUTE cannot be used as a source when inserting into a table variable."

CREATE TABLE MyTest
(Test1 VARCHAR(20))

INSERT INTO MyTest
 (Test1)
VALUES
 ('Hello')

CREATE PROCEDURE GetTest
AS
BEGIN
  SELECT Test1
  FROM MyTest
END

DECLARE @tabCaptureTheResults TABLE (Data VARCHAR(20))

INSERT @tabCaptureTheResults
EXEC GetTest

This results in:
Server: Msg 197, Level 15, State 1, Line 4
EXECUTE cannot be used as a source when inserting into a table variable.

CREATE TABLE #CaptureTheResults (Data VARCHAR(20))

INSERT #CaptureTheResults
EXEC GetTest

SELECT * FROM #CaptureTheResults

This results in 'Hello' being returned, exactly as required.
Whilst this works, it isn't exactly going to be efficient. Anyone know of any better ways of doing this within TSQL?

UPDATE:
As of SQL 2005 both cases seem to work - Hurrah!

Labels: Development, SQLServer2000

// posted by Jane @ 12:36 PM   

Comments:

Post a Comment

I encountered a TSQL issue this morning that I knew looked familiar. When doing SELECT LEN(@strName) it was returning less characters than I expected. So, when doing SELECT LEN(' ') I got a result of 0 instead of 1, when I did SELECT LEN('Jane ') I got 4 rather than 5. I'd forgotten about the fact that it trims off white space before returning data. Thanks to Adam Machanic I refound DATALENGTH which does what I'd expect.

LEN defined in SQL Server Books online as Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.

DATALENGTH defined as Returns the number of bytes used to represent any expression. DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data. The DATALENGTH of NULL is NULL.

Labels: Development, SQLServer, SQLServer2000

// posted by Jane @ 3:04 PM   

Comments:

Post a Comment

I've been debugging and unit testing some involved stored procedures, and I've left a trail of PRINT statements all over the place. I was having real difficulties tracking the last 2 down, so I wrote the following SQL to help me track them down.

SELECT o.name
FROM syscomments c
INNER JOIN sysobjects o
ON c.id = o.id
WHERE o.xtype = 'P'
AND text LIKE 'PRINT @'

Labels: Development, SQLServer, SQLServer2000

// posted by Jane @ 10:28 AM   

Comments:

From SQL Code it looks like

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%PRINT @%'

would work as well as it probably a cleaner way

# posted by  Jane : 4:40 PM

 

Post a Comment

Tony Rogerson has an interesting article about Collations in SQL Server, examples and restrictions, explaining how and why they happen and what they mean. This is a problem I've faced again and again, especially when bringing back customer's data. One of the slight annoyances I have with Visual Studio 2003 is that when generating create scripts for tables it puts the collation into the table creation scripts, so I often do a search of the database for COLLATE and remove them, taking the code from

CREATE TABLE [dbo].[Person]
( [PersonID] [int] NOT NULL ,
[Name] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

to

CREATE TABLE [dbo].[Person]
( [PersonID] [int] NOT NULL ,
[Name] [varchar] (10) NOT NULL
) ON [PRIMARY]

We let our databases take the default collation from the server, rather than forcing our own. This can still lead to issues and I've had the

Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
error when doing casts rather than joins. To get around this I've used COLLATE database_default to fix it, for example
SELECT @strName = "THE" + ISNULL(CAST(@intID AS VARCHAR(10)),'NULL') COLLATE database_default

Labels: Development, SQLServer, SQLServer2000

// posted by Jane @ 11:09 AM   

Comments:

Post a Comment

One of the products I help to look after has recently gained its own upgrade process - so you put in the CD and "stuff just happens". As part of this the database gets upgraded, and whilst doing so it is put into single user mode. Unfortunately, one customer has had a problem, and the upgrade has failed. We thought it might be in single user mode, but weren't convinced, so to check we ran


sp_dboption [databasename], 'single user'


which resulted in


OptionName   CurrentSetting
-----------  --------------
single user  on


to change this we simple issued


sp_dboption [databasename], 'single user', false


and checked again and this time it said


OptionName   CurrentSetting
-----------  --------------
single user  off


Success! Now to fix the process...

Labels: Development, SQLServer, SQLServer2000

// posted by Jane @ 1:58 PM   

Comments:

Post a Comment