J0YgzsvC

1USE [Demo]
2GO
3
4BEGIN 
5	-- DECLARE Parameters
6	DECLARE @CommitChange BIT = 1;  -- 1 to commit transaction, 0 to cancel transaction
7	DECLARE @NewTableName VARCHAR(20) = 'Customer';
8	DECLARE @SchemaName VARCHAR(10) = 'dbo';
9	-- Taking a snapshot before the change
10	SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @NewTableName
11
12	BEGIN TRANSACTION
13		BEGIN TRY
14			-- Create table if not exist 
15			IF NOT EXISTS (
16				SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @NewTableName 
17			)
18				BEGIN
19					CREATE TABLE [dbo].[Customer]
20					(
21						Id INT PRIMARY KEY IDENTITY NOT NULL,
22						FirstName NVARCHAR(10) NOT NULL,
23						LastName NVARCHAR(10) NOT NULL,
24						[Address] NVARCHAR(50) NOT NULL,
25						CreateDate DATE NOT NULL DEFAULT GETDATE(),
26						UpdateDate DATE NULL
27					)
28				END
29			
30			-- Take a snapshot after change
31			SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @NewTableName
32
33			-- Determine whether to commit or cancel transaction based on parameters
34			IF @CommitChange = 1
35				BEGIN
36					SELECT 'Committing Transaction...'
37					COMMIT TRANSACTION
38				END
39			ELSE
40				BEGIN
41					SELECT 'Cancelling Transaction...'
42					ROLLBACK TRANSACTION
43				END
44		END TRY
45		BEGIN CATCH
46			SELECT
47				ERROR_NUMBER(),
48				ERROR_SEVERITY(),
49				ERROR_STATE(),
50				ERROR_PROCEDURE(),
51				ERROR_LINE(),
52				ERROR_MESSAGE()
53			
54			SELECT 'Aborting Transaction...'
55			ROLLBACK TRANSACTION
56		END CATCH
57
58		-- Take a snapshot of final result
59		SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @NewTableName
60		SELECT * FROM dbo.Customer
61END