Checklist

There are a few things we will always do when creating a stored procedure.

  1. Initialize all parameters to NULL.
  2. Check for missing parameters before doing any other logic.
  3. Make sure to assign a value to variables before using them.
  4. Check @@error after every DML statement.
  5. Before running an UPDATE or DELETE, make sure there exists something to UPDATE or DELETE.
  6. If you have more than one DML statement that needs to run, you need a TRANSACTION.
    • BEGIN the transaction before the 1st DML statement, ROLLBACK if any branch fails, and COMMIT if they all worked.

Testing your SP

  1. Make sure you test every branch of your flow.
    • Test with “good” parameters, missing parameters, AND some weird parameters.
    • ❓ The next section has examples of weird parameters that will break DML statements, or you could enter parameters of the wrong data type, or a nonsense value like a negative grade.
  2. If you make any changes to your code, run all your tests again! Sometimes fixing one thing will break something else.
  3. Remember: testing isn’t about proving it works. Testing is about trying to break your code! Then we fix the break, and our code is stronger and more thorough. 💪

Testing error branches

How can we effectively test the “error” branches of our code?

  • For INSERTs, try INSERTing bad data:
    • a FK that doesn’t have a matching PK, or
    • a duplicate PK, or
    • a NULL in a required field, or
    • something that violates a CHECK constraint.
  • For UPDATEs:
    • you could UPDATE the data to a FK without a matching PK, or
    • change the PK to a value that already exists, or
    • change the PK of a record that has child records, or
    • change a required field to NULL, or
    • violate some CHECK constraint.
  • For DELETEs:
    • you could try deleting a record that has a child record.

Example pseudocode: creating a student

What is the minimum number of test I’d need to test this SP?

IF (parameters are missing)
	RAISERROR -- branch 1
ELSE
	BEGIN TRANSACTION 
	INSERT into Registration ...
	IF (INSERT failed)
		RAISERROR
		ROLLBACK TRANSACTION -- branch 2
	ELSE
		UPDATE Student.BalanceOwing ...
		IF (UPDATE failed)
		    RAISERROR
		    ROLLBACK TRANSACTION -- branch 3
		ELSE
		    COMMIT TRANSACTION --  branch 4
		
-- testing each brach: 
	-- 1. missing parameter(s)
	-- 2. failed insert
	-- 3. successful insert but failed update
	-- 4. successful insert & update