title: SQL Unit Testing Creating test for Stored procedure that does not necessarily return a value
body: <p>I am new to sql unit testing but I have written my first test and trying to make sure this is a sensible test. So I have a stored procedure that does a simply Update(if exists) or insert(if not exists). Using TSQLUnit, I wrote the test below to test my stored procedure called <strong>spModifyData</strong>. What the test is designed to do is verify that when an existing ID is passed, a new record is not created in the database. </p>
  
  <pre><code>ALTER PROCEDURE [dbo].[ut_TestspModifyData] 
  AS
  BEGIN 
      SET NOCOUNT ON;
       -- Setup the test conditions by inserting test data
       DECLARE 
                  @newidone uniqueidentifier,
                  @newidtwo uniqueidentifier,
                  @newidthree uniqueidentifier,
                  @ExpectedID uniqueidentifier,
                  @ActualID uniqueidentifier
  
                  SET @ActualID = '13E7C741-9A04-4E84-B604-141874A6A9B4'
                  SET @ExpectedID = '13E7C741-9A04-4E84-B604-141874A6A9B4'
                  SET @newidone = newID()
                  SET @newidtwo = newID()
                  SET @newidthree = newID()
  
                   INSERT INTO DataSource( [DataSourcePrimarySource],[DataSourceName],[DataSourceRecordCreateDate] 
            ,[DataSourceStatus] ,[DataSourceIsActive]) VALUES ('PRIMARY SOURCE ONE', 'XYZ', GETDATE() , @newidone, 1)
  
    -- Exercise the test
   EXEC  spModifyDataSource @ActualID , 'PRIMARY SOURCE ONETWO', 'BBB', @newIDone, 0  
  
   -- Assert expectations
   IF (@ExpectedID != @ActualID)
                  EXEC dbo.tsu_Failure 'ModifyData failed.' 
  
  -- Teardown
   -- Implicitly done via ROLLBACK TRAN
  
  END
  </code></pre>