Peter Lalovsky
Keep it simple
Peter Lalovsky
Keep it simple
Database Development T-SQL Unit Testing

T-SQL: Quick and simple Unit Testing of a Stored Procedure with Predefined Parameters

by |Published

Let’s say that we have a SP with predefined parameters, i.e. @Parameter1 can hold values 1, 2 or 3; @Parameter2 can hold values of A, B, C or D and so on.

This script creates all the possible combinations for the parameters of the SP and executes the SP as many times as many combinations we have.

Prepare the playground

--** Create database - Start **--
USE [master];
GO
 
IF (DB_ID('PL') IS NOT NULL)
	BEGIN
		ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	END
GO
 
CREATE DATABASE PL;
GO
 
USE PL;
GO
--** Create database - End **--

--** Create Stored Procedure - Start **--
IF (OBJECT_ID('PL.dbo.usp__UnitTesting__Test1', 'P') IS NOT NULL)
	BEGIN DROP PROC dbo.usp__UnitTesting__Test1; END
GO

CREATE PROCEDURE dbo.usp__UnitTesting__Test1
(
	@Parameter1 INT -- 1, 2, 3
	, @Parameter2 NVARCHAR(32) -- A, B, C, D
	, @Parameter3 VARCHAR(64) -- Y, Z
	, @Parameter4 DATE -- 1968-12-01, 1965-05-17, 1963-08-26, 1965-11-14
	, @Parameter5 TIME -- 12:35:07.533 09:12:18.413
	, @Parameter6 DECIMAL(10, 2) -- 3.14, 5.76
) AS
	BEGIN
	
		IF (@Parameter3 != 'Z')
			BEGIN
				SELECT
					@Parameter1 AS [@Parameter1]
					, @Parameter2 AS [@Parameter2]
					, @Parameter3 AS [@Parameter3]
					, @Parameter4 AS [@Parameter4]
					, @Parameter5 AS [@Parameter5]
					, @Parameter6 AS [@Parameter6];
			END
		ELSE
			BEGIN
				SELECT 1/0; -- Divide by zero error encountered.
			END
	END
GO
--** Create Stored Procedure - End **--

--** Create table to store the parameter's values - Start **--
IF (OBJECT_ID('tempdb.dbo.#Parameters', 'U') IS NOT NULL)
	BEGIN DROP TABLE #Parameters; END
GO

CREATE TABLE #Parameters
(
	ParameterID INT IDENTITY(1, 1) PRIMARY KEY
	, RankLabel INT
	, RankValue INT
	, [Label] VARCHAR(128)
	, [Value] VARCHAR(128)
);
GO

INSERT #Parameters ([Label], [Value])
SELECT						'Parameter1', '1'
UNION ALL SELECT	'Parameter1', '2'
UNION ALL SELECT	'Parameter1', '3'
UNION ALL SELECT	'Parameter2', 'A'
UNION ALL SELECT	'Parameter2', 'B'
UNION ALL SELECT	'Parameter2', 'C'
UNION ALL SELECT	'Parameter2', 'D'
UNION ALL SELECT	'Parameter3', 'Y'
UNION ALL SELECT	'Parameter3', 'Z'
UNION ALL SELECT	'Parameter4', '1968-12-01'
UNION ALL SELECT	'Parameter4', '1965-05-17'
UNION ALL SELECT	'Parameter4', '1963-08-26'
UNION ALL SELECT	'Parameter4', '1965-11-14'
UNION ALL SELECT	'Parameter5', '12:35:07.533'
UNION ALL SELECT	'Parameter5', '09:12:18.413'
UNION ALL SELECT	'Parameter6', '3.14'
UNION ALL SELECT	'Parameter6', '5.76';
GO

UPDATE #Parameters
SET
	RankLabel = U.L
	, RankValue = U.V
FROM
	(
		SELECT
			ParameterID AS I
			, DENSE_RANK() OVER (ORDER BY [Label]) AS L
			, ROW_NUMBER() OVER (PARTITION BY [Label] ORDER BY [Value]) AS V
		FROM #Parameters
	) AS U
WHERE ParameterID = U.I;
GO

--select * from #Parameters order by RankLabel, RankValue
--** Create table to store the parameter's values - End **--

#Parameters

Then we add dynamically the columns for the parameters

--** Variables - Start **--
DECLARE
	-- Store the original values
	@MaxRankLabel INT
	, @Counter INT = 1
	-- To be reseted with the original values when needed and used in the loops
	, @MaxRankLabelLoop INT
	, @CounterLoop INT = 1
	-- 
	, @StoredProcedureName SYSNAME = 'dbo.usp__UnitTesting__Test1'
	, @DynamicSQL1 VARCHAR(MAX)
	, @DynamicSQL2 VARCHAR(MAX);

SELECT @MaxRankLabel = MAX(RankLabel)
FROM #Parameters;

SELECT @MaxRankLabelLoop = @MaxRankLabel;
--** Variables - End **--





--** Create table to store the combinations - Start **--
-- Reset the variables
SELECT
	@CounterLoop = @Counter
	, @MaxRankLabelLoop = @MaxRankLabel;

IF (OBJECT_ID('tempdb.dbo.#Combinations', 'U') IS NOT NULL)
	BEGIN DROP TABLE #Combinations; END

CREATE TABLE #Combinations (ID INT IDENTITY(1, 1) PRIMARY KEY);

WHILE (@CounterLoop <= @MaxRankLabelLoop)
	BEGIN
		SET @DynamicSQL1 = '
ALTER TABLE #Combinations
ADD C' + CAST(@CounterLoop AS VARCHAR(10)) + ' VARCHAR(128);
';
		EXEC (@DynamicSQL1);
		
		SET @CounterLoop += 1;
	END

--select * from #Combinations
--** Create table to store the combinations - End **--

#Combinations (1)

As we have 6 parameters, we add 6 columns.

Then we insert the combinations into the temp table

--** Insert the combinations - Start **--
SET @DynamicSQL1 = '
INSERT #Combinations
SELECT
'
-- Loop the SELECT clause - Start
-- Reset the variables - Start
SELECT
	@CounterLoop = @Counter
	, @MaxRankLabelLoop = @MaxRankLabel;
-- Reset the variables - End

WHILE (@CounterLoop <= @MaxRankLabelLoop)
	BEGIN
		SET @DynamicSQL1 += 
		CASE WHEN @CounterLoop > 1 THEN '
	, ' ELSE '	' END +
		'''@'' + T' + CAST(@CounterLoop AS VARCHAR(10)) + '.[Label] + '' = '''''' + T' + CAST(@CounterLoop AS VARCHAR(10)) + '.[Value] + '''''''' AS C' + CAST(@CounterLoop AS VARCHAR(10)) + '';
	
		SET @CounterLoop += 1;
	END
-- Loop the SELECT clause - End

SET @DynamicSQL1 += '
FROM'

-- Loop the FROM clause - Start
-- Reset the variables - Start
SELECT
	@CounterLoop = @Counter
	, @MaxRankLabelLoop = @MaxRankLabel;
-- Reset the variables - End

WHILE (@CounterLoop <= @MaxRankLabelLoop)
	BEGIN
		SET @DynamicSQL1 += 
		CASE WHEN @CounterLoop > 1 THEN 'CROSS APPLY' ELSE '' END + '
	(
		SELECT DISTINCT [Label], [Value]
		FROM #Parameters
		WHERE RankLabel = ' + CAST(@CounterLoop AS VARCHAR(10)) + '
	) AS T' + CAST(@CounterLoop AS VARCHAR(10)) + '
	';
	
		SET @CounterLoop += 1;
	END
-- Loop the FROM clause - End

--print @DynamicSQL1
EXEC (@DynamicSQL1);
--select * from #Combinations;
--** Insert the combinations - End **--

#Combinations (2)

We execute the combinations

--** Read the combinations - Start **--
-- Reset the variables - Start
SELECT
	@CounterLoop = @Counter
	, @MaxRankLabelLoop = @MaxRankLabel;
-- Reset the variables - End

SET @DynamicSQL1 = '';

WHILE (@CounterLoop <= @MaxRankLabelLoop)
	BEGIN
		SET @DynamicSQL1 += 
		CASE WHEN @CounterLoop = 1 THEN '
SELECT ''EXEC ' + @StoredProcedureName + ' ''' + ' + 
	' ELSE '
	+ '', '' + ' END
	+ 'C' + CAST(@CounterLoop AS VARCHAR(10)) + '';
	
		SET @CounterLoop += 1;
	END

	SET @DynamicSQL1 += '
FROM #Combinations;'

--print @DynamicSQL1
--EXEC (@DynamicSQL1);
--** Read the combinations - End **--





--** Execute the statements - End **--
IF (OBJECT_ID('tempdb.dbo.#ErrorLog', 'U') IS NOT NULL)
	BEGIN DROP TABLE #ErrorLog; END

CREATE TABLE #ErrorLog
(
	LogDateTime DATETIME
	, ExecStatement VARCHAR(MAX)
	, ErrorNumber INT
	, ErrorSeverity INT
	, ErrorState INT
	, ErrorProcedure NVARCHAR(128)
	, ErrorLine INT
	, ErrorMessage NVARCHAR(4000)
);

SET @DynamicSQL2 = '
DECLARE @C_ExecStatement VARCHAR(MAX);

DECLARE ExecStatement_Cursor CURSOR FOR 
	' + @DynamicSQL1 + ';

OPEN ExecStatement_Cursor;

FETCH NEXT FROM ExecStatement_Cursor 
INTO @C_ExecStatement

WHILE @@FETCH_STATUS = 0
BEGIN
	BEGIN TRY
		-- http://stackoverflow.com/questions/866484/how-to-suppress-the-select-output-of-a-stored-procedure-called-from-another-stor
		/* Assume this table matches the output of your procedure */
		--DECLARE @tmpNewValue TABLE (C1 VARCHAR(MAX), C2 VARCHAR(MAX), C3 VARCHAR(MAX), C4 VARCHAR(MAX), C5 VARCHAR(MAX), C6 VARCHAR(MAX))
		--INSERT @tmpNewValue
		
		EXEC (@C_ExecStatement);
	END TRY
	BEGIN CATCH
		INSERT #ErrorLog
		(
			LogDateTime
			, ExecStatement
			, ErrorNumber
			, ErrorSeverity
			, ErrorState
			, ErrorProcedure
			, ErrorLine
			, ErrorMessage
		)
		SELECT
			GETDATE()
			, @C_ExecStatement
			, ERROR_NUMBER()
			, ERROR_SEVERITY()
			, ERROR_STATE()
			, ERROR_PROCEDURE()
			, ERROR_LINE()
			, ERROR_MESSAGE();
	END CATCH
	
	FETCH NEXT FROM ExecStatement_Cursor
	INTO @C_ExecStatement
END

CLOSE ExecStatement_Cursor;
DEALLOCATE ExecStatement_Cursor;
';

--print @DynamicSQL2
EXEC (@DynamicSQL2);
GO
--** Execute the ststements - End **--

Unit Testing

And finally we get the errors

SELECT *
FROM #ErrorLog;
GO

Get the Errors

And the full script, divided by 3 separate executions

----------********** Prepare the playground - Start **********----------
--** Create database - Start **--
USE [master];
GO
 
IF (DB_ID('PL') IS NOT NULL)
	BEGIN
		ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	END
GO
 
CREATE DATABASE PL;
GO
 
USE PL;
GO
--** Create database - End **--

--** Create Stored Procedure - Start **--
IF (OBJECT_ID('PL.dbo.usp__UnitTesting__Test1', 'P') IS NOT NULL)
	BEGIN DROP PROC dbo.usp__UnitTesting__Test1; END
GO

CREATE PROCEDURE dbo.usp__UnitTesting__Test1
(
	@Parameter1 INT -- 1, 2, 3
	, @Parameter2 NVARCHAR(32) -- A, B, C, D
	, @Parameter3 VARCHAR(64) -- Y, Z
	, @Parameter4 DATE -- 1968-12-01, 1965-05-17, 1963-08-26, 1965-11-14
	, @Parameter5 TIME -- 12:35:07.533 09:12:18.413
	, @Parameter6 DECIMAL(10, 2) -- 3.14, 5.76
) AS
	BEGIN
	
		IF (@Parameter3 != 'Z')
			BEGIN
				SELECT
					@Parameter1 AS [@Parameter1]
					, @Parameter2 AS [@Parameter2]
					, @Parameter3 AS [@Parameter3]
					, @Parameter4 AS [@Parameter4]
					, @Parameter5 AS [@Parameter5]
					, @Parameter6 AS [@Parameter6];
			END
		ELSE
			BEGIN
				SELECT 1/0; -- Divide by zero error encountered.
			END
	END
GO
--** Create Stored Procedure - End **--

--** Create table to store the parameter's values - Start **--
IF (OBJECT_ID('tempdb.dbo.#Parameters', 'U') IS NOT NULL)
	BEGIN DROP TABLE #Parameters; END
GO

CREATE TABLE #Parameters
(
	ParameterID INT IDENTITY(1, 1) PRIMARY KEY
	, RankLabel INT
	, RankValue INT
	, [Label] VARCHAR(128)
	, [Value] VARCHAR(128)
);
GO

INSERT #Parameters ([Label], [Value])
SELECT						'Parameter1', '1'
UNION ALL SELECT	'Parameter1', '2'
UNION ALL SELECT	'Parameter1', '3'
UNION ALL SELECT	'Parameter2', 'A'
UNION ALL SELECT	'Parameter2', 'B'
UNION ALL SELECT	'Parameter2', 'C'
UNION ALL SELECT	'Parameter2', 'D'
UNION ALL SELECT	'Parameter3', 'Y'
UNION ALL SELECT	'Parameter3', 'Z'
UNION ALL SELECT	'Parameter4', '1968-12-01'
UNION ALL SELECT	'Parameter4', '1965-05-17'
UNION ALL SELECT	'Parameter4', '1963-08-26'
UNION ALL SELECT	'Parameter4', '1965-11-14'
UNION ALL SELECT	'Parameter5', '12:35:07.533'
UNION ALL SELECT	'Parameter5', '09:12:18.413'
UNION ALL SELECT	'Parameter6', '3.14'
UNION ALL SELECT	'Parameter6', '5.76';
GO

UPDATE #Parameters
SET
	RankLabel = U.L
	, RankValue = U.V
FROM
	(
		SELECT
			ParameterID AS I
			, DENSE_RANK() OVER (ORDER BY [Label]) AS L
			, ROW_NUMBER() OVER (PARTITION BY [Label] ORDER BY [Value]) AS V
		FROM #Parameters
	) AS U
WHERE ParameterID = U.I;
GO

--select * from #Parameters order by RankLabel, RankValue
--** Create table to store the parameter's values - End **--
----------********** Prepare the playground - End **********----------
----------********** Unit Testing - Start **********----------
--** Variables - Start **--
DECLARE
	-- Store the original values
	@MaxRankLabel INT
	, @Counter INT = 1
	-- To be reseted with the original values when needed and used in the loops
	, @MaxRankLabelLoop INT
	, @CounterLoop INT = 1
	-- 
	, @StoredProcedureName SYSNAME = 'dbo.usp__UnitTesting__Test1'
	, @DynamicSQL1 VARCHAR(MAX)
	, @DynamicSQL2 VARCHAR(MAX);

SELECT @MaxRankLabel = MAX(RankLabel)
FROM #Parameters;

SELECT @MaxRankLabelLoop = @MaxRankLabel;
--** Variables - End **--





--** Create table to store the combinations - Start **--
-- Reset the variables
SELECT
	@CounterLoop = @Counter
	, @MaxRankLabelLoop = @MaxRankLabel;

IF (OBJECT_ID('tempdb.dbo.#Combinations', 'U') IS NOT NULL)
	BEGIN DROP TABLE #Combinations; END

CREATE TABLE #Combinations (ID INT IDENTITY(1, 1) PRIMARY KEY);

WHILE (@CounterLoop <= @MaxRankLabelLoop)
	BEGIN
		SET @DynamicSQL1 = '
ALTER TABLE #Combinations
ADD C' + CAST(@CounterLoop AS VARCHAR(10)) + ' VARCHAR(128);
';
		EXEC (@DynamicSQL1);
		
		SET @CounterLoop += 1;
	END

--select * from #Combinations
--** Create table to store the combinations - End **--





--** Insert the combinations - Start **--
SET @DynamicSQL1 = '
INSERT #Combinations
SELECT
'
-- Loop the SELECT clause - Start
-- Reset the variables - Start
SELECT
	@CounterLoop = @Counter
	, @MaxRankLabelLoop = @MaxRankLabel;
-- Reset the variables - End

WHILE (@CounterLoop <= @MaxRankLabelLoop)
	BEGIN
		SET @DynamicSQL1 += 
		CASE WHEN @CounterLoop > 1 THEN '
	, ' ELSE '	' END +
		'''@'' + T' + CAST(@CounterLoop AS VARCHAR(10)) + '.[Label] + '' = '''''' + T' + CAST(@CounterLoop AS VARCHAR(10)) + '.[Value] + '''''''' AS C' + CAST(@CounterLoop AS VARCHAR(10)) + '';
	
		SET @CounterLoop += 1;
	END
-- Loop the SELECT clause - End

SET @DynamicSQL1 += '
FROM'

-- Loop the FROM clause - Start
-- Reset the variables - Start
SELECT
	@CounterLoop = @Counter
	, @MaxRankLabelLoop = @MaxRankLabel;
-- Reset the variables - End

WHILE (@CounterLoop <= @MaxRankLabelLoop)
	BEGIN
		SET @DynamicSQL1 += 
		CASE WHEN @CounterLoop > 1 THEN 'CROSS APPLY' ELSE '' END + '
	(
		SELECT DISTINCT [Label], [Value]
		FROM #Parameters
		WHERE RankLabel = ' + CAST(@CounterLoop AS VARCHAR(10)) + '
	) AS T' + CAST(@CounterLoop AS VARCHAR(10)) + '
	';
	
		SET @CounterLoop += 1;
	END
-- Loop the FROM clause - End

--print @DynamicSQL1
EXEC (@DynamicSQL1);
--select * from #Combinations;
--** Insert the combinations - End **--





--** Read the combinations - Start **--
-- Reset the variables - Start
SELECT
	@CounterLoop = @Counter
	, @MaxRankLabelLoop = @MaxRankLabel;
-- Reset the variables - End

SET @DynamicSQL1 = '';

WHILE (@CounterLoop <= @MaxRankLabelLoop)
	BEGIN
		SET @DynamicSQL1 += 
		CASE WHEN @CounterLoop = 1 THEN '
SELECT ''EXEC ' + @StoredProcedureName + ' ''' + ' + 
	' ELSE '
	+ '', '' + ' END
	+ 'C' + CAST(@CounterLoop AS VARCHAR(10)) + '';
	
		SET @CounterLoop += 1;
	END

	SET @DynamicSQL1 += '
FROM #Combinations;'

--print @DynamicSQL1
--EXEC (@DynamicSQL1);
--** Read the combinations - End **--





--** Execute the statements - End **--
IF (OBJECT_ID('tempdb.dbo.#ErrorLog', 'U') IS NOT NULL)
	BEGIN DROP TABLE #ErrorLog; END

CREATE TABLE #ErrorLog
(
	LogDateTime DATETIME
	, ExecStatement VARCHAR(MAX)
	, ErrorNumber INT
	, ErrorSeverity INT
	, ErrorState INT
	, ErrorProcedure NVARCHAR(128)
	, ErrorLine INT
	, ErrorMessage NVARCHAR(4000)
);

SET @DynamicSQL2 = '
DECLARE @C_ExecStatement VARCHAR(MAX);

DECLARE ExecStatement_Cursor CURSOR FOR 
	' + @DynamicSQL1 + ';

OPEN ExecStatement_Cursor;

FETCH NEXT FROM ExecStatement_Cursor 
INTO @C_ExecStatement

WHILE @@FETCH_STATUS = 0
BEGIN
	BEGIN TRY
		-- http://stackoverflow.com/questions/866484/how-to-suppress-the-select-output-of-a-stored-procedure-called-from-another-stor
		/* Assume this table matches the output of your procedure */
		--DECLARE @tmpNewValue TABLE (C1 VARCHAR(MAX), C2 VARCHAR(MAX), C3 VARCHAR(MAX), C4 VARCHAR(MAX), C5 VARCHAR(MAX), C6 VARCHAR(MAX))
		--INSERT @tmpNewValue
		
		EXEC (@C_ExecStatement);
	END TRY
	BEGIN CATCH
		INSERT #ErrorLog
		(
			LogDateTime
			, ExecStatement
			, ErrorNumber
			, ErrorSeverity
			, ErrorState
			, ErrorProcedure
			, ErrorLine
			, ErrorMessage
		)
		SELECT
			GETDATE()
			, @C_ExecStatement
			, ERROR_NUMBER()
			, ERROR_SEVERITY()
			, ERROR_STATE()
			, ERROR_PROCEDURE()
			, ERROR_LINE()
			, ERROR_MESSAGE();
	END CATCH
	
	FETCH NEXT FROM ExecStatement_Cursor
	INTO @C_ExecStatement
END

CLOSE ExecStatement_Cursor;
DEALLOCATE ExecStatement_Cursor;
';

--print @DynamicSQL2
EXEC (@DynamicSQL2);
GO
--** Execute the ststements - End **--
----------********** Unit Testing - End **********----------
----------********** Get the Errors - Start **********----------
SELECT *
FROM #ErrorLog;
GO
----------********** Get the Errors - End **********----------

Keep it simple :-)

AUTHOR

Peter Lalovsky
Canada based bilingual data engineer and data architect with more than 20 years experience in IT, including the last 10 years in Data Engineering and Business Intelligence. Consultant and owner of zPL Concept Inc.

LinkedIn | GitHub

You may also like

Leave a comment

Your email address will not be published. Required fields are marked *

Time limit exceeded. Please complete the captcha once again.