Untitled
unknown
sql
a year ago
5.3 kB
5
Indexable
SET LANGUAGE 'English' SET DATEFORMAT ymd SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT, XACT_ABORT ON SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS OFF GO -- -- Set transaction isolation level -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO -- -- Start Transaction -- BEGIN TRANSACTION -- -- Inserting data into table Report.Reports -- IF NOT EXISTS (SELECT * FROM [Report].[Reports] WHERE Name = 'Correspondence Success Report') BEGIN INSERT INTO [Report].[Reports] ([CategoryId] ,[Name] ,[Description] ,[SQL] ,[Layout] ,[Active] ,[Custom_Report] ,[CreatedBy] ,[CreatedUTCDate] ,[LastModifiedBy] ,[LastModifiedUtc]) VALUES (2 --Accounting ,'Correspondence Success Report' ,'A report to see when a correspondence messages has sent successfully' ,'EXEC Report.CorrespondenceSuccessReport' ,'[[1]]' ,1 ,0 ,'system' ,GETDATE() ,NULL ,GETDATE()) IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END DECLARE @ReportId INT = @@IDENTITY; -- -- Inserting data into ReportParameter -- DECLARE @endDateId INT; SET @endDateId = (SELECT TOP(1) Id from [Report].[Parameter] where ParameterName='EndDate'); INSERT INTO [Report].[ReportParameter] ([ReportId] ,[ParameterId] ,[DefaultValue] ,[FieldLabel] ,[SQLParameter] ,[NumOrder] ,[Mandatory] ,[CreatedBy] ,[CreatedUTC] ,[Modifiedby] ,[ModifiedUTC]) VALUES (@ReportId ,@endDateId ,NULL ,NULL ,'@d_end_date' ,30 ,1 ,'system' ,GETDATE() ,NULL ,GETDATE()) IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END -- -- Inserting data into ReportParameter -- DECLARE @startDateId INT; SET @startDateId = (SELECT TOP(1) Id from [Report].[Parameter] where ParameterName='StartDate'); INSERT INTO [Report].[ReportParameter] ([ReportId] ,[ParameterId] ,[DefaultValue] ,[FieldLabel] ,[SQLParameter] ,[NumOrder] ,[Mandatory] ,[CreatedBy] ,[CreatedUTC] ,[Modifiedby] ,[ModifiedUTC]) VALUES (@ReportId ,@startDateId ,NULL ,NULL ,'@d_start_date' ,20 ,1 ,'system' ,GETDATE() ,NULL ,GETDATE()) IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END -- -- Inserting data into CorrespondenceName -- DECLARE @correspondenceNameId INT; SET @correspondenceNameId = (SELECT TOP(1) Id from [Report].[Parameter] where ParameterName='CorrespondenceName'); -- -- Inserting data into ReportParameter -- INSERT INTO [Report].[ReportParameter] ([ReportId] ,[ParameterId] ,[DefaultValue] ,[FieldLabel] ,[SQLParameter] ,[NumOrder] ,[Mandatory] ,[CreatedBy] ,[CreatedUTC] ,[Modifiedby] ,[ModifiedUTC]) VALUES (@ReportId ,@correspondenceNameId ,'All' ,NULL ,'@v_correspondence_name' ,40 ,1 ,'system' ,GETDATE() ,NULL ,GETDATE()) IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END -- -- Inserting data into ReportDataTable -- INSERT INTO [Report].[ReportDataTable] ([ReportID] ,[DataTableID] ,[DataSetTypeId] ,[Title]) VALUES (@ReportId ,1 ,1 ,NULL) IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END INSERT INTO [Report].[OrgReports](ReportId ,[OrgId] ,[Active] ,[CreatedBy] ,[CreatedUtcDate] ,[LastModifiedBy] ,[LastModifiedUtc]) SELECT @ReportId, org.Id, 1, 'system', GETUTCDATE(), null, null FROM ( SELECT Id FROM Organisations ) AS org IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END END -- -- Commit Transaction -- IF @@TRANCOUNT>0 COMMIT TRANSACTION SET NOEXEC OFF GO
Editor is loading...
Leave a Comment