sql server - Visual Studio 2010 Unit Test DB -

i imported sql server 2008 db script , created new vs 2010 project create unit testing database. has built in functionality checking return row count , scalar variables

what need know how can clear entire database prior running procedures because check new inserted row requires tell row# check.

here code unit test calling stored proc insert addres:

-- database unit test dbo.spinsertaddress declare @rc int, @street varchar (60), @city varchar (50), @state char (20), @zip varchar (10), @intersection1 varchar (60), @intersection2 varchar (60), @addressid int;  select @rc = 0,        @street = 'streetname',        @city = null,        @state = null,        @zip = null,        @intersection1 = null,        @intersection2 = null;  execute @rc = [dbo].[spinsertaddress] @street, @city, @state, @zip, @intersection1, @intersection2, @addressid output;  --select @rc rc, --       @addressid addressid; select * address; 

here image of vs unit testing:

to reiterate question how clear database or remove whatever inserted after procedure runs?

also others interested in unit testing db via vs here link: create database unit tests functions, triggers, , stored procedures

solution 1

i use practice it, depends on production policy, not commit if called ut. everytime rollback.

solution 2

if not possible because procedure must affect tables or something, can records , call store procedure own rollback procedure , called ut

solution 3

also, in case, recommended work environment ut, own database in order tfs agent runs build , run test without touching critical data.

please comment if want review of these answers, can more if tell me approach it's more applicable use.