On a high level, I consider the stored procedure to be what in electronics is called a black box - you define the inputs and the expected outputs.

All of the following would be done in the development environment using a recent backup from production.

Begin a new query, DECLARE the input parameters and SET those parameters to sample values. Write some T-SQL using those parameters to produce the desired results.

For INSERT, UPDATE or DELETE sp's I would also insert BEGIN TRAN and ROLLBACK TRAN with a few "SELECT *" statements before and after the T-SQL so I can verify the data changes.

At this point you have a working batch & you can determine if results & performance meet the objectives.

Once the batch is working, comment out the DECLARE's and SET's and add the CREATE PROC statement at the top of the code. Create & then execute the sp with various combinations of parameters to verify that the outputs are still correct. I would also run it with some inappropriate input values to see if I can break it.

After all that, you can remove any TRAN and SELECT * statements and the sp is ready for QA testing.