Unit testing SQL stored procedures

gistfile1.txt

BEGIN TRAN

DELETE FROM [oltp.cq.logging.2.10.0.x].dbo.AUD_EXC
DELETE FROM [oltp.cq.logging.2.10.0.x].dbo.AUD_ACT

USE [oltp.cq.stage.2.10.0.x]

DELETE FROM QCD_CYC
DELETE FROM QCD_EVT
DELETE FROM QUA_CYC
DELETE FROM QUA_EVT

/*
SELECT * FROM INT_CYC
SELECT * FROM INT_EVT
*/

SELECT 'INT_CYC', count(*) FROM INT_CYC
SELECT 'INT_EVT', count(*) FROM INT_EVT

--SELECT 'before', USR_E10_Date, USR_E30_Date, USR_E40_Date, USR_E90_Date FROM DVW_REP_DIM_CYC WHERE USR_CYC_Reference = 'cyc integration update test_8790_02-01-2013_100'

DECLARE @test NVARCHAR(MAX)
SET @test = 'QCP-CYC-0330'

IF 
	(SELECT USR_E10_Date FROM DVW_REP_DIM_CYC WHERE USR_CYC_Reference = 'cyc integration ignore update test_8790_02-01-2013_100') <> '2012-12-31 00:00:00.000'
BEGIN
	SELECT 'TEST FAILED: (setup incorrect)', @test
END

EXEC xmlISP_INT_CYC_Integration
EXEC xmlISP_IDI_CYC_Distribution

--SELECT 'after', USR_E10_Date, USR_E30_Date, USR_E40_Date, USR_E90_Date FROM DVW_REP_DIM_CYC WHERE USR_CYC_Reference = 'cyc integration update test_8790_02-01-2013_100'
DECLARE @count INT
SELECT @count = COUNT(*) FROM QCD_CYC 
INNER JOIN 
	DVW_CFG_LUT_QTP
ON
	USR_QTP_Guid = DVW_CFG_LUT_QTP.SYS_REC_Guid
INNER JOIN
	QUA_CYC
ON
	USR_CYC_Guid = QUA_CYC.SYS_REC_Guid
WHERE
	LUT_QTP_Code = '000'

IF 
	(SELECT USR_E10_Date FROM DVW_REP_DIM_CYC WHERE USR_CYC_Reference = 'cyc integration ignore update test_8790_02-01-2013_100') <> '2012-12-31 00:00:00.000'
	OR @count <> 1
BEGIN
	SELECT 'TEST FAILED:', @test
END

--SELECT 'ACT', * FROM [oltp.cq.logging.2.10.0.x].dbo.AUD_ACT
--SELECT 'EXC', * FROM [oltp.cq.logging.2.10.0.x].dbo.AUD_EXC

/*
DELETE FROM INT_CYC
DELETE FROM INT_EVT
*/

ROLLBACK TRAN