November 2, 2008 at 3:19 am
Hi,
I need to test stored procedures with various input parameters to make sure no input make it crash. I want to do this without the procedure leaving any data in database.
It should be possible to write a procedure for this purpose. But before i start i would like to know if some one already did this or tried to do this?
November 2, 2008 at 6:59 am
Hi,
Wrap everything in a transaction:
BEGIN TRANSACTION
... do whatever it is you want to do
ROLLBACK
... and I am trusting you are not testing on production data but have some other reason you cannot tamper with the (test) data 😉
HTH!
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 2, 2008 at 7:24 am
Elizabeth has the best idea, but this can cause locks if you leave the transaction open.
So if you start with BEGIN TRAN, run the proc, and then start doing selects from that same connection, locks will be held on the resources that were changed until you run ROLLBACK. This can cause contention in some places.
The other solution is to have rollback statements ready.
So if you update MyTable set MyValue = 1 Where MyID = 10, you can have another statement ready to undo things:update MyTable set MyValue = 0 Where MyID = 10, assuming 0 was the old value.
November 2, 2008 at 10:05 am
Thanks for your replies.
The purpose of my testing is that i will deploy other peoples stored procedures into production enviroment and i would like to have a testing procedure that check the type of the input parameters and then run an automated test with different parameter values.
I whant to do this without editing any code. It will be no time for that. But i whant to perform blackbox testing of the procedure a a whole.
If i wrap it in a transaction. I gues that will not help when xp_cmdshell is used.
November 2, 2008 at 11:52 am
Hi again Fredrik,
You have not provided us with much information about the environment where these tests will be conducted but you will be running the tests in some sort of test environment with test data, right?
You didn't tell us why the data cannot be changed; if the database is not too big - could an option be for you to have a ready backup to restore after each test run?
If you don't care about the outcome of your stored procedures I suppose you could set the database to read-only. Not sure what your tests would accomplish but anyway.
And you're right, whatever you do outside of SQL Server with xp_cmdshell cannot be rolled back.
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 3, 2008 at 6:41 am
From the sounds of it, you're planning on testing against your production system. That's a huge mistake. The chances of messing something up are just too high (and I'm assuming you mess stuff up less than I do). You really should get a backup of the database to a different location and run your tests there. Then you can run a series of data changes without worrying about what you're doing to production. You can rollback as needed or reset from the backup again.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply