Search code examples
sqlsql-serverssms

Can you run or debug a SQL Server stored procedure without side effects?


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?


Solution

  • 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!