CODEWITHSUNDEEP
sqlunit-testingsql-server-2005testingstored-procedures
user1277680
user1277680

Reputation: 61

Test stored procedure without affecting database

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

For example, there is an insert query in the SP, which will change the data of the database.

Is there anyway to solve this problem?

Upvotes: 6

Views: 3195

Answers (2)

Valamas
Valamas

Reputation: 24719

You could run the stored procedure in a transaction. Use this script by placing your statements between the comment lines. Run the whole script, your transaction will be in an uncommitted state. Then, highlight the line ROLLBACK or COMMIT and execute either accordingly to finish.

Always have backups.

If possible work in a sandbox away from your clients data as a matter of principle.

Be aware that you could be locking data which could be holding up other sql statements by your client while you are deciding 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

Upvotes: 4

John Saunders
John Saunders

Reputation: 161773

If the SP is meant to change data, and if you don't permit the data to change, then how will you "test" the SP? Will you just make sure it doesn't die? What if it returns no errors, but inserts no data?

You can follow a similar path to what Valamas suggested, but you will also need to actually test the SP. For instance, if particular data are meant to be inserted based on particular parameter values, then you'll have to:

  1. Start a transaction
  2. Create any test data in the database
  3. Call the SP with the particular parameter values
  4. Still within the transaction, check the database to see if the correct rows were inserted
  5. Roll back the transaction

I can't show you the code, but I have had success in doing the above in code in .NET, 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 Database Unit Test feature of Visual Studio Database Projects. I simply did the steps above in code, using ADO.NET and the SqlTransaction class to control the transaction.

Upvotes: 2

Related Questions