ZnPhFk7k

1-- создание таблицы для логирования:
2CREATE TABLE [log].[errors]
3(
4ErrorNumber INT NOT NULL,
5ErrorSeverity INT NOT NULL,
6ErrorState INT NOT NULL,
7ErrorProcedure NVARCHAR(128) NOT NULL,
8ErrorLine INT NOT NULL,
9ErrorMessage nvarchar(4000),
10InsertDate DATETIME DEFAULT GETDATE()
11);
12
13-------------------------------------------------------------------------
14
15-- создание внутренней хранимой процедуры
16Create Procedure usp_InternalStoredProc
17@SQL NVARCHAR(MAX)
18As
19Begin
20	Begin Transaction TranExcpHandlingTest_2011
21	Begin Try
22
23			EXEC(@SQL);
24
25		-- закрытие транзакции
26		Commit Transaction TranExcpHandlingTest_2011
27	End Try
28	Begin Catch
29		If @@TRANCOUNT > 0 Rollback Transaction TranExcpHandlingTest_2011
30		Print 'In catch block of internal stored procedure.... throwing the exception';
31		-- инициирование исключения
32		THROW
33	End Catch
34End
35
36
37-- создание внешней хранимой процедуры
38Create Procedure usp_ExternalStoredProc
39@SQL NVARCHAR(MAX)
40As
41Begin
42	Begin Try
43		-- вызов внутренней процедуры
44		Exec usp_InternalStoredProc @SQL
45	End Try
46	Begin Catch
47		--Print 'In catch block of external stored procedure.... throwing the exception';
48		INSERT INTO [log].[errors] (ErrorNumber
49									, ErrorSeverity
50									, ErrorState
51									, ErrorProcedure
52									, ErrorLine
53									, ErrorMessage)
54		SELECT
55			 ERROR_NUMBER() AS ErrorNumber
56			,ERROR_SEVERITY() AS ErrorSeverity
57			,ERROR_STATE() AS ErrorState
58			,ERROR_PROCEDURE() AS ErrorProcedure
59			,ERROR_LINE() AS ErrorLine
60			,ERROR_MESSAGE() AS ErrorMessage;
61		THROW
62	End Catch
63End
64
65
66---------------------------------------------------------------------------------------------------------------------
67
68CREATE PROCEDURE [dbo].[etl_merge_buffer_table]
69	-- Add the parameters for the stored procedure here
70	@table_name nvarchar(100),
71	@last_row_column_name NVARCHAR(100) = '_last_date_update',
72	@print_query INT = 0
73AS
74BEGIN
75	-- SET NOCOUNT ON added to prevent extra result sets from
76	-- interfering with SELECT statements.
77	SET NOCOUNT ON;
78
79    -- Insert statements for procedure here
80	BEGIN TRY 
81	 BEGIN TRANSACTION 
82		
83		DECLARE @SQL NVARCHAR(MAX) = N'';
84
85		DECLARE @SQL_2 NVARCHAR(MAX) = N'';
86		DECLARE @SQL_3 NVARCHAR(MAX) = N'';
87
88		-- Если хранимая процедура etl_merge_' + @table_name + ' существует, то выполняем ее, иначе берем универсальную процедуру
89		IF exists (SELECT 1
90					FROM  sysobjects
91					WHERE  id = object_id('[dbo].[etl_merge_' + @table_name + ']')
92							AND   type = 'P')
93		
94		   BEGIN
95				--SET @SQL = '[dbo].[etl_merge_' + @table_name + ']';
96				--EXEC sp_executesql @SQL;
97				EXEC usp_ExternalStoredProc @SQL
98			END
99		ELSE
100			BEGIN 
101
102				--EXEC [dbo].[etl_merge] @table_name;
103				SET @SQL_2 = 'EXEC [dbo].[etl_merge] ' + @table_name;
104				EXEC usp_ExternalStoredProc @SQL_2
105
106				IF exists (SELECT 1
107							FROM  sysobjects
108							WHERE  id = object_id('[dbo].[' + @table_name + ']')
109									AND   type = 'U')
110					BEGIN
111						--EXEC [dbo].[etl_merge] @table_name, 'dbo', @last_row_column_name = @last_row_column_name, @print_query = @print_query;
112						SET @SQL_3 = 'EXEC [dbo].[etl_merge] ' + @table_name + ', ' + '''' + 'dbo' +'''' + ', ' + @last_row_column_name + ', ' + CONVERT(VARCHAR,@print_query);
113						EXEC usp_ExternalStoredProc @SQL_3
114					END
115
116			END
117
118		-- EXECUTE sp_executesql @sql_insert
119	--	EXEC sp_executesql @SQL, @table_name = @table_name;
120
121	 COMMIT TRANSACTION 
122	END TRY  
123	BEGIN CATCH  
124	 DECLARE @ErrorMessage NVARCHAR(4000);  
125	 DECLARE @ErrorSeverity INT;  
126	 DECLARE @ErrorState INT;  
127  
128	 SELECT   
129	  @ErrorMessage = ERROR_MESSAGE(),  
130	  @ErrorSeverity = ERROR_SEVERITY(),  
131	  @ErrorState = ERROR_STATE();  
132
133	 ROLLBACK TRANSACTION 
134  
135	 -- Use RAISERROR inside the CATCH block to return error  
136	 -- information about the original error that caused  
137	 -- execution to jump to the CATCH block.  
138	 RAISERROR (@ErrorMessage, -- Message text.  
139		@ErrorSeverity, -- Severity.  
140		@ErrorState -- State.  
141		); 
142	END CATCH 
143END