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 **--

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 **--

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 **--

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 **--

And finally we get the errors
SELECT * FROM #ErrorLog; GO

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 :-)