In my daily work, I use a lot of SQL logic (oracle stored procedures and SQL functions).
To limit the code regression on the implemented code I make what I call “integration scripts” : C# scripts that call the SP with given parameters and compare the result with the intended one and alert me if the result aren’t the intended ones.
I run these scripts after each modification to validate that I didn’t break everything.
This works pretty well, better than nothing, but it looks like makeshift job.
Is there any practice better than this one to validate SQL based code ?
If a unit test breaks, you usually know exactly what went wrong; when an integration test breaks, it’s a hint that a piece of your process has something wrong with it. If you’re comfortable with this and your SP isn’t incredibly complex, the integration tests from C# should be fine.
A few things to consider :
Use a unit testing framework. Calling them “C# Scripts” makes me wonder if you’re doing these tests without a proper unit testing framework.
Look into rolling back the results of any database mutations after the test has run. Here’s an older example but should work in any test runner
1
In less words, this sounds like unit testing. Your “C# scripts” are the units of pass/failure.
Is there any practice better than this one to validate SQL based code ?
You shouldn’t be concerned with validating the actual SQL code. All you really should care about is the validity of the data and the result. You should be decoupling your unit tests from the actual underlying mechanism (an RDBMS in this case), so that your unit testing is interchangeable if the underlying data source became different, or even a different type (think: flat file, service request, API calls, etc.).
An example
An automaker shouldn’t care about the molecular properties and testing of the metal they use for their cars. They should care about the numbers and deceleration during an impact test of the car. Think big picture.
So the better question here shouldn’t be “how do I test my SQL logic”, but it should be “how do I test and validate my logic and data”.