How to test CASE statements?

[mreaybeaton]

Hi,

I appreciate this probably isn't the place to ask, if there is a better place please let me know.

I have a legacy Stored Procedure that selects some data and inserts into a table. However there are quite a few case statements that I would like to test and I wondered how that should be done.

insert into [dbo].[utb_ITP]
...
case when [sme].[Reporting Countries] = 'Multiple Countries'
     then 'United Kingdom-Multiple Countries'
     when patindex('%-$',[sme].[Reporting Countries) > 1
     then left([sme].[Reporting countries], (pat index('%-%', [sme].[Reporting Countries]) - 1 ))
     else [sme].[Reporting Countries]
end as [SVC_COUNTRY]

I think the samples I have seen have all been mocking data and testing functions, but I haven't really seen examples like a case statement. Do I test the final output or the component parts? So I would first test

[sme].[Reporting Countries] = 'Multiple Countries'
     then 'United Kingdom-Multiple Countries'

In that I would use an AssertEqualsTable where the Expected value would be 'United Kingdom-Multiple Countries' and then the insert into the Actual would run the first part of the case, something like:

insert into #Expected values ('United Kingdom-Multiple Countries')

insert into #Actual
select case [sme].[Reporting Countries] = 'Multiple Countries'
            then 'United Kingdom-Multiple Countries'
end
from   [SourceTable] as [sme]
where [sme].[Reporting Countries] = 'Multiple Countries'

exec AssertEqualsTable ...

[mbt1]

This is not an issue. Questions are best asked on the tSQLt Google group, or, as you have done already, on StackOverflow. I wrote up an answer there: https://stackoverflow.com/questions/69954692/unit-testing-case-statements/69956350#69956350