Issue with INSERT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Issue with INSERT

Author

Topic

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2010-06-16 : 09:34:39

I'm using a tool I found online called TSQLUnit (http://tsqlunit.sourceforge.net) to unit test my stored procs. As the tool is written, I can only note one failure per unit test. In other words, if I need to test a stored proc that has 50 input parameters, I have to write a separate unit test for each parameter. This results in 50 unit tests, identical except for the bit that tests each individual parameter.

This is really tedious, so I was trying to get around it without altering any of the TSQLUnit code. When a failure of the unit test is noted, the user is supposed to call the failure proc, as such:

IF (@ActualValue1 <> @TestValue1)
BEGIN
EXECUTE [dbo].[tsu_failure] 'Value1 Failure Message'
END

which passes the failure message to another proc, which inserts it along with the test name into a table called tsuFailures.

The problem is, if I try to call this proc more than once during a unit test, such as:

IF (@ActualValue1 <> @TestValue1)
BEGIN
EXECUTE [dbo].[tsu_failure] 'Value1 Failure Message'
END

IF (@ActualValue2 <> @TestValue2)
BEGIN
EXECUTE [dbo].[tsu_failure] 'Value2 Failure Message'
END

The only message that gets reported is the Value2 message. This has to do with how the message is processed by a second proc, tsu__private_addFailure, before it is inserted into the tsuFailures table. As I mentioned before, I have over 50 input parameters that need to be tested, and this is only for one stored proc - there are 40 or 50 total that we need to test. So I tried inserting directly into the tsuFailures table.

It doesn't work. I can run a script that inserts directly into the tsuFailures table when run as a single script, but when I run that script within the tSQLUnit testing framework, nothing gets inserted. I was wondering if I'm overlooking something really simple. Here's the script I'm running to try and get around the above procedure calls:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ut_mutipleInsertTest]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ut_mutipleInsertTest]
GO

USE [TulipODS]
GO

create proc ut_mutipleInsertTest
as
begin
DECLARE @Id as int
,@test as varchar(255)
,@errorMessage as varchar(255)

SET @Id = (SELECT MAX(testResultID) FROM tsuTestResults)
SET @test = 'ut_mutipleInsertTest'
SET @errorMessage = 'Failure message test 1'

INSERT INTO tsuFailures( test, message, testResultID)
VALUES(@test, @errorMessage, @id)

SET @Id = (SELECT MAX(testResultID) FROM tsuTestResults)
SET @test = 'ut_mutipleInsertTest'
SET @errorMessage = 'Failure message test 2'

INSERT INTO tsuFailures( test, message, testResultID)
VALUES(@test, @errorMessage, @id)
end

Any ideas will be greatly appreciated.

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources