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

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


Then we add dynamically the columns for the parameters

#Combinations (1)

As we have 6 parameters, we add 6 columns.

Then we insert the combinations into the temp table

#Combinations (2)

We execute the combinations

Unit Testing

And finally we get the errors

Get the Errors

And the full script, divided by 3 separate executions

Keep it simple :-)