'Can you run or debug a SQL Server stored procedure without side effects? [closed]

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?

sqlsql-serverssms


Solution 1:[1]

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://docs.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!

Solution 2:[2]

Create a SQL Server database project in VS, add a script file, paste the SQL code which you want to debug inside this block

BEGIN TRANSACTION
/*SQL code here */
ROLLBACK TRANSACTION

Then you can use all the debug functionality of VS.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 AradiaJoanne
Solution 2 Dale K

Related Questions