Well, I used to work at a place where they used https://tsqlt.org/ for testing it worked great

I dont know the technical detail of how to set it up, it was already setup when I worked there

But basically, we wrote SQL script that included statements to

1. create the db structure, tables or views

2. insert statement to enter test data (you can insert corner cases etc..)

3. ran the function or procedure

4. ran an assert to confirm if results are to our expectation

test script were ran by the CI/CD process