Database Unit Testing Comparison?

[SnapDive]

Not sure if this is the right forum for this, but here goes...

I have a Visual Studio 2008 GDR Database Unit Test (Data Dude) with
some TSQL that calls a sproc. I then have a single select statement
that produces a 1-row result set with 2 columns. I want to pass the
test if the values are equal and fail if it is not equal. I do not
understand how to config/code this, can anyone point me in the right
direction?

Thanks.

[Dan Guzman]

> I have a Visual Studio 2008 GDR Database Unit Test (Data Dude) with
> some TSQL that calls a sproc. I then have a single select statement
> that produces a 1-row result set with 2 columns. I want to pass the
> test if the values are equal and fail if it is not equal.

If the values are known (which they should be in a unit test, IMHO), you can
use 2 Scalar Value assertions. Check resultset 1, row 1, columns 1 and 2
for the same value. Also, add an assertion for row count 1 too.

Alternatively, you can perform assertions in your unit test script in T-SQL.
This provides the flexibility in that the values don't need to be known
beforehand. Something like:

DECLARE @result_row TABLE (col1 int, col2 int)

INSERT INTO @result_row(col1, col2)
EXEC dbo.usp_MyProc;

IF NOT (SELECT COUNT(*) FROM @result_row) = 1
BEGIN
RAISERROR ('Assertion failed: one row expected', 16, 1);
END

IF NOT EXISTS(SELECT *
FROM @result_row
WHERE col1 = col2)
BEGIN
RAISERROR ('Assertion failed: column values not equal', 16, 1);
END

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

[SnapDive]

I finally got around to trying your suggestion out. That is a great
one. Thanks!