asked on
CREATE PROCEDURE [dbo].[ImportMatters]
AS
SET XACT_ABORT, NOCOUNT ON
DECLARE @ID VARCHAR(25) = NULL
DECLARE @OpenedDate DATETIME = NULL
DECLARE @StartTranCount INT
/* Create the cursor for the list of matters to be inserted or updated */
DECLARE MatterList cursor for
SELECT
LTRIM(RTRIM(a.ClientCode)) + '-' + LTRIM(RTRIM(a.MatterCode)) AS ID,
DateOpened,
FROM import_matter a
OPEN MatterList
FETCH NEXT FROM MatterList INTO
@ID,
@OpenedDate,
WHILE @@FETCH_STATUS = 0
BEGIN TRY
SELECT @StartTranCount = @@TRANCOUNT
If @StartTranCount = 0
BEGIN TRANSACTION IMPORTMATTERS
/* Check to see if the matter already exists */
IF EXISTS (SELECT MatterID FROM Matters WHERE MatterID = @ID)
BEGIN
<DO SOME UPDATES HERE>
/* Insert record into MattsTracking indicating successful update */
INSERT INTO MattsTracking (TrackingTime, TrackDesc, Matters, Matterid, Success)
VALUES (GETDATE(),'Update',@Matters,RTRIM(@ID),'Y')
END
ELSE -- Matter doesn't exist, create a new matter
BEGIN
<DO AN INSERT HERE>
/* Insert record in MattsTracking indicating successful insert */
INSERT INTO MattsTracking (TrackingTime, TrackDesc, Matters, Matterid, Success)
VALUES (GETDATE(),'Insert Matter',@Matters,RTRIM(@ID),'N')
END
IF @StartTranCount=0
COMMIT TRANSACTION MATTERS
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 AND @StartTranCount = 0
BEGIN
/* Insert error record tracking info into MattsTracking and rollback all transactions */
INSERT INTO MattsTracking (TrackingTime, TrackDesc, Matters, Matterid, Success, ErrorMessage)
VALUES (GETDATE(),'Insert',@Matters,RTRIM(@ID),'N', ERROR_MESSAGE())
ROLLBACK TRANSACTION IMPORTMATTERS
END
END CATCH
/* Insert the assigned professionals relate records - calls procedure that inserts records into MattPpof table */
BEGIN TRY
SELECT @StartTranCount = @@TRANCOUNT
IF @StartTranCount=0
BEGIN TRANSACTION MATTPROF
--set some variables and then call procedures to populate MattProf
EXEC ImportMattProfs @Matters, @ProfID, @AssignedType
IF @StartTranCount = 0
COMMIT TRANSACTION MATTPROF
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 AND @StartTranCount = 0
BEGIN
/* Insert error record tracking info into SC_MattsTracking and rollback all transactions */
INSERT INTO MattsTracking (TrackingTime, TrackDesc, Matters, Matterid, Success, ErrorMessage)
VALUES (GETDATE(),'MattProf',@Matters,RTRIM(@ID),'N', ERROR_MESSAGE())
ROLLBACK TRANSACTION IMPORT_MATTPROF
END
END CATCH
END
FETCH NEXT FROM MatterList INTO
@ID,
@OpenedDate
END
CLOSE MatterList
DEALLOCATE MatterList
SET NOCOUNT OFF