Or you can simply implement it so that when your code runs it first checks that the stored procedures exist and have the same definition as the one stored in the source code; and to drop/recreate them if that's not the case.

In which case the stored procedures are in your "main" code repo and "deploying" them simply means running the software. Though your point about unit tests stands.