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 user_table_after_live_mode FROM user_table; -- Cleanup DROP TABLE dbo.user_table; DROP PROCEDURE dbo.PREVIEW_EXAMPLE; GO
I'm looking for feedback on this code and design pattern, and/or if other solutions to the same problem exist in different formats.