There are unit-testing frameworks for the purpose. For SQL Server there are several, [0] I've worked briefly with one called tSQLt [1] and was quite impressed. Frameworks also exist for other RDBMSs. [0]

[0] https://en.wikipedia.org/wiki/List_of_unit_testing_framework...

[1] https://tsqlt.org/user-guide/quick-start/

I'd test them the same way I test other database stuff: run automated tests which spin up a database server for the duration, then run migrations to install the schema and stored procedures, then execute tests using your programming language of choice.

I still mostly use Django for PostgreSQL projects and the Django default test framework is great at running a temporary PostgreSQL (or MySQL or SQLite).

For Postgres, there’s pgTAP: https://pgtap.org/