I would like to be able to see the effects of running a Stored Procedure that someone else has written or other SQL code on a production database, in order to debug it. I would like to be able to see the updated values in the table without it actually affecting their data.
Is there a way that I would be able to see these changes in this way?
You can do the following:
BEGIN TRANSACTION
/*SQL code here */
ROLLBACK TRANSACTION
This will allow you to test changes without committing them to the database.
You can read more about this in the documentation https://learn.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-ver15
Remember to do any investigative queries inside the transaction as outside of it the changes will revert. You could also create a testing database too by cloning an existing one, then you can test without worry!