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
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
This award recognizes a member of Experts Exchange who has made outstanding contributions to the community within their first year as an expert. The Rookie of the Year is awarded to a new expert who has the highest number of quality contributions.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.