All geek questions in one place

Test a stored procedure without affecting the database

I need to run a test on a stored procedure in a client database. Is there a way to check the stored procedure without affecting the data in the database?

For example, in SP there is an insertion request that will modify database data.

Is there any way to solve this problem?

+6
source share
2 answers

You can run the stored procedure in a transaction. Use this script by placing your posts between comment lines. Run the whole script, your transaction will be in an uncommitted state. Then select the ROLLBACK or COMMIT line and complete it accordingly.

Always have backups.

If possible, work in the sandbox from your customer data in principle.

Keep in mind that you can block data that could be held by other SQL queries by your client when you decide whether to commit or rollback.

BEGIN TRANSACTION MyTransaction GO -- INSERT SQL BELOW -- INSERT SQL ABOVE GO IF @@ERROR != 0 BEGIN PRINT '--------- ERROR - ROLLED BACK ---------' ROLLBACK TRANSACTION MyTransaction END ELSE BEGIN PRINT '--------- SCRIPT EXECUTE VALID ---------' PRINT '--------- COMPLETE WITH ROLLBACK OR COMMIT NOW! ---------' --ROLLBACK TRANSACTION MyTransaction --COMMIT TRANSACTION MyTransaction END
+3
source

If the SP is designed to change data, and if you do not allow the data to change, then how do you "check" the SP? You just make sure that he does not die? What if it does not return any errors, but does not insert data?

You can follow a similar path to what Valamas suggested, but you will also need to test the SP. For example, if specific data is intended to be inserted based on certain parameter values, you need to:

  • Start transaction
  • Create any test data in the database
  • Call SP with specific parameter values
  • Inside the transaction, check the database to see if the correct rows were inserted.
  • Transaction rollback

I can't show you the code, but I managed to do it higher in the .NET code using the Visual Studio unit test framework. One could do the same with NUnit or any other unit test framework. I did not use the unit test database function for Visual Studio Database Projects. I just did the steps above in the code using ADO.NET and the SqlTransaction class to control the transaction.

+2
source

Source: https://habr.com/ru/post/911057/

More articles:


All Articles