Database stored procedure with a "preview mode"

A fairly common pattern in the database application I work with is a need to create a stored procedure for a report or utility that has a "preview mode". When such a procedure does updates, this parameter indicates that the results of the action should be returned, but the procedure should not actually perform the updates to the database.

One way to accomplish this is to simply write an if statement for the parameter, and have two complete code blocks; one of which does updates and returns data and the other just returns the data. But this is undesirable because of the code duplication and a relatively low degree of confidence that the preview data is actually an accurate reflection of what would happen with an update.

The following example attempts to leverage transaction savepoints and variables (which are not affected by transactions, in contrast to temp tables which are) to use just a single block of code for the preview mode as the live update mode.

Note: Transaction rollbacks are not an option since this procedure call may itself be nested in a transaction. This is tested on SQL Server 2012.

```
CREATE TABLE dbo.user_table (a int);
GO

CREATE PROCEDURE [dbo].[PREVIEW_EXAMPLE] (
@preview char(1) = 'Y'
) AS

CREATE TABLE #dataset_to_return (a int);

BEGIN TRANSACTION; -- preview mode required infrastructure
DECLARE @output_to_return TABLE (a int);
SAVE TRANSACTION savepoint;

-- do stuff here
INSERT INTO dbo.user_table (a)
OUTPUT inserted.a INTO @output_to_return (a)
VALUES (42);

-- catch preview mode
IF @preview = 'Y'
ROLLBACK TRANSACTION savepoint;

-- save output to temp table if used for return data
INSERT INTO #dataset_to_return (a)
SELECT a FROM @output_to_return;
COMMIT TRANSACTION;

SELECT a AS proc_return_data FROM #dataset_to_return;
RETURN 0;
GO

-- Examples
EXEC dbo.PREVIEW_EXAMPLE @preview = 'Y';
SELECT a AS user_table_after_preview_mode FROM user_table;

EXEC dbo.PREVIEW_EXAMPLE @preview = 'N';
SELECT a AS use

There are several flaws to this approach:

-
The term "preview" can be quite misleading in most cases, depending on the nature of the data being operated on (and that changes from operation to operation). What is to ensure that the current data being operated on will be in that same state between the time the "preview" data is gathered and when the user comes back 15 minutes later -- after grabbing some coffee, stepping outside for a smoke, walking around the block, coming back in, and checking something on eBay -- and realizes that they didn't click the "OK" button to actually perform the operation and so finally clicks the button?

Do you have a time-limit on proceeding with the operation after the preview is generated? Or possibly a way to determine that the data is in the same state at modification time as it was at initial SELECT time?

-
This is a minor point as the example code could have been done hastily and not represent a true use-case, but why would there be a "Preview" for an INSERT operation? That could make sense when inserting multiple rows via something like INSERT...SELECT and there could be a variable number of rows inserted, but this doesn't make much sense for a singleton operation.

-

this is undesirable because of ... a relatively low degree of confidence that the preview data is actually an accurate reflection of what would happen with an update.

Where exactly does this "low degree of confidence" come from? While it is possible to update a different number of rows than show up for a SELECT when multiple tables are JOINed and there is duplication of rows in a result set, that should not be an issue here. Any rows that should be affected by an UPDATE are selectable on their own. If there is a mismatch then you are doing the query incorrectly.

And those situations where there is duplication due to a JOINed table that matches multiple rows in the table that will be updated are not situations where a "Preview" would be generated. And if there is an occasion where this is the case, then it needs to be explained to the user that they are updated a subset of the report that is repeated within the report so that it does not appear to be an error if someone is only looking at the number of affected rows.

-
For the sake of completeness (even though the other answers mentioned this), you are not using the TRY...CATCH construct so could easily run into issues when nesting these calls (even if not using Save Points, and even if not using Transactions). Please see my answer to the following Question, here on DBA.SE, for a template that handles transactions across nested Stored Procedure calls:

Are we required to handle Transaction in C# Code as well as in stored procedure

-
EVEN IF the issues noted above were accounted for, there is still a critical flaw: for the short-period of time the operation is being performed (i.e. prior to the ROLLBACK), any dirty-read queries (queries using WITH (NOLOCK) or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ) can grab data that isn't there a moment later. While anyone using dirty-read queries should already be aware of this and have accepted that possibility, operations such as this greatly increase the chances of introducing data anomalies that are very difficult to debug (meaning: how much time do you want to spend trying to find a problem that has no apparent direct cause?).

-
A pattern like this also degrades system performance by both increasing blocking by taking out more locks, and generating more Transaction Log activity. (I see now that @MartinSmith also mentioned these 2 issues in a comment on the Question.)

Additionally, if there are Triggers on the tables being modified, that could be quite a bit of additional processing (CPU and Physical/Logical reads) that is unnecessary. Triggers would also further increase the chances of data anomalies resulting from dirty reads.

-
Related to the point noted directly above -- increased locks -- the use of the Transaction increases the likelihood of running into deadlocks, especially if Triggers are involved.

-
A less severe issue that should relate only to the less-likely scenario of INSERT operations: the "Preview" data might not be the same as what is inserted with regards to column values determined by DEFAULT Constraints (Sequences / NEWID() / NEWSEQUENTIALID()) and IDENTITY.

-
There is no need for the additional overhead of writing the contents of the Table Variable into the Temporary Table. The ROLLBACK wouldn't affect the data in the Table Variable (which is why you said you were using Table Variables in the first place), so it would make more sense to simply SELECT FROM @output_to_return; at the end, and then don't even bother creating the Temporary Table.

-
Just in case this nuance of Save Points is not known (hard to tell from the example code as it only shows a single Stored Procedure): you need to use unique Save Point names so that the `R

StackExchange Database Administrators Q#136916, answer score: 12