I am in a healthcare project, where majority of the business written in stored procedures. How can i test the stored procedures to check the data is flowing per business or not? Is it through any testing tool i should use to achieve this? If Yes, then please tell me the tools name. I need to test the data field by field. Please confirm.
You have two major options:
1: Write your own unit testing scenarios. You can follow this link for better guidance: https://msdn.microsoft.com/en-us/library/jj851212(v=vs.103).aspx
2: You can use some third party tools to test your stored procedures. Some of these tools are:
TST simplifies the task of writing and running test automation for T-SQL code. Written in T-SQL it provides an API similar to those provided by other Unit Testing frameworks. Can produce test reports in XML format. It provides automatic rollback, dataset comparison support, etc.
If you want to do SQL Server unit testing in T-SQL, and run it as part of your automated builds, SQL Test is the place to start. It's simple to set up, you don’t need extra tools, and you don't need to learn new technologies. SQL Test also comes with pre-written SQL Cop tests, to help you enforce best practices for database development and run static analysis tests.
Importantly, because tSQLt allows you to implement unit tests in T-SQL, you don’t have to switch between various tools to create the code and unit tests.
You can also find many other tools, similar to these.