test

create table testdatabase
(
	id int primary key identity(1, 1),
	[start_date] date not null,
	[end_date] date null
)

go

insert into testdatabase (start_date, end_date) 
values 
(GETDATE(), null),
(GETDATE(), '2022-04-30')

go

-- Test Case - 1 (end_date = null)

declare @param_date_1 date = '2022-05-15'
declare @param_date_2 date = '2022-04-30'
declare @param_date_3 date = '2022-04-11'

select * 
from testdatabase t 
where 
	t.id = 1 and 
	dbo.is_valid_date(@param_date_1, t.start_date, t.end_date) = 1

select * 
from testdatabase t 
where 
	t.id = 1 and 
	dbo.is_valid_date(@param_date_2, t.start_date, t.end_date) = 1


select * 
from testdatabase t 
where 
	t.id = 1 and 
	dbo.is_valid_date(@param_date_3, t.start_date, t.end_date) = 1

-- Test Case - 2 (end_date = not null)

select * 
from testdatabase t 
where 
	t.id = 2 and 
	dbo.is_valid_date(@param_date_1, t.start_date, t.end_date) = 1

select * 
from testdatabase t 
where 
	t.id = 2 and 
	dbo.is_valid_date(@param_date_2, t.start_date, t.end_date) = 1


select * 
from testdatabase t 
where 
	t.id = 2 and 
	dbo.is_valid_date(@param_date_3, t.start_date, t.end_date) = 1