Hello All,
I am working on a stored procedure which is responsible to check some validations and insert the data into tables. Here is the SP I am working on.
DECLARE @total int,
@ProjJobBookNo Varchar(50)
SET @ProjJobBookNo ='2011.0715'
SELECT @Total = COUNT(*)
FROM TableA WHERE ProjJobBookNo = @ProjJobBookNo AND LoadToPODS_LF = 'N'
IF @TOTAL = 0
BEGIN
CREATE TABLE DBO.#TMP_LOAD_HCA
(ID INT
,EVENT_GUID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID()
,loc_guid uniqueidentifier
,BEG_station_GUID UNIQUEIDENTIFIER
,END_STATION_GUID UNIQUEIDENTIFIER
,BEGIN_SERIES NUMERIC(16,0)
,END_sERIES NUMERIC(16,0)
)
INSERT INTO #TMP_LOAD_HCA(ID, LOC_GUID, BEGIN_SERIES, END_SERIES)
SELECT ID, LOC_GUID, BEGIN_SERIES, END_SERIES
FROM TableA GEO
UPDATE T
SET T.BEGIN_SERIES_GUID = S.SERIES_GUID
FROM #TMP_LOAD_HCA t JOIN ASSET.DBO.SERIES S ON T.BEGIN_SERIES = S.SERIES_ID
WHERE BEGIN_SERIES_GUID IS NULL
UPDATE T
SET T.END_SERIES_GUID = S.SERIES_GUID
FROM #TMP_LOAD_HCA t JOIN ASSET.DBO.SERIES S ON T.END_SERIES = S.SERIES_ID
WHERE END_SERIES_GUID IS NULL
update t
set t.BEG_station_GUid = sp.station_GUid from #TMP_LOAD_HCA t
join station_point sp on t.begin_station = sp.station
update t
set t.END_station_GUid = SP.station_GUid from #TMP_LOAD_HCA t
join station_point sp on t.end_station = sp.station
SET @Total = 0
SELECT @TOTAL = COUNT(*) FROM #TMP_LOAD_HCA WHERE (BEG_STATION_GUID IS NULL OR
END_STATION_GUID IS NULL)
IF @Total > 0
BEGIN TRY
INSERT INTO Asset.dbo.LOCATION(LOCATION_GUID)
SELECT LOC_GUID FROM #TMP_LOAD_HCA
INSERT INTO Asset.dbo.STATION_POINT(STATION_GUID, STATION, SERIES_GUID, LOCATION_GUID,
MEASURE, CREATE_DATE, Effective_From_Date, PODS_USER)
SELECT STATION_GUID, STATION, SERIES_GUID, LOC_GUID, MEASURE, GETDATE(),
GETDATE(), 'HCA_LOAD' FROM #TMP_LOAD_HCA
print '2. Created ' + convert(varchar(500),@total) + ' new station points'
END TRY
BEGIN CATCH
DELETE FROM ASSET.DBO.LOCATION WHERE LOCATION_GUID IN
(SELECT LOCATION_GUID FROM #TMP_LOAD_HCA)
PRINT 'ERROR LOADING STATION POINTS'
PRINT 'Data issues found, cannot continue.'
PRINT 'One or more columns are missing critical data. '
PRINT 'Please execute Update_Staging_HCABoundary to correct the data.'
END CATCH
IF EXISTS(SELECT STATION_GUID FROM ASSET.DBO.STATION_POINT WHERE STATION_GUID IN(SELECT BEG_STATION_GUID FROM #TMP_LOAD_HCA))
BEGIN
INSERT INTO EVENT_RANGE(EVENT_GUID, FEATURE_ID, STATION_GUID_BEGIN, STATION_GUID_END,
PODS_USER, CREATE_DATE, EFFECTIVE_FROM_DATE)
SELECT EVENT_GUID, 'HCAB',BEG_STATION_GUID, END_STATION_GUID,
'HCA_LOAD',GETDATE(), GETDATE()
FROM #TMP_LOAD_HCA
IF @@ERROR = 0
INSERT INTO HCA_CA_SEGMENT
(EVENT_GUID, TYPE_CL, DETERMINATION_DATE,
SOURCE_GCL, COMMENTS, PIR_Footage, DESCRIPTION)
SELECT TEMP.EVENT_GUID, TEMP.TYPE_CL, TEMP.DETERMINATION_DATE,
TEMP.SOURCE_GCL, TEMP.COMMENTS, TEMP.PIR_FOOTAGE, cl.DESCRIPTION
FROM #TMP_LOAD_HCA temp join Asset.dbo.HCA_TYPE_CL cl on
temp.TYPE_CL = cl.code
END
END
END
Here in the SP, I am creating a temp table #TMP_LOAD_HCA and inserting the data into it. After inserting, I am checking whether Beg_Station_GUID and END_Station_GUID are nulls. If they are nulls, insert the data into Location and Station_Point tables. While
inserting the data into station_Point table, if it errors out it shouldn't go to the next steps which is inserting the data into event_Range and HCA_CA_Segment. The process should stop and delete the data from Location and print the error messages as shown
in the code. If the insert into Station_point succeeds then only it should go to insert statement for event_range and HCA_CA_Segment tables. If the insert statement for event_Range fails it shouldn't go to insert statement for HCA_cA_Segment. It should stop
and return an error message. Here I tried with Try Catch block and @@error...but it is not working as expected. The bottom line is error handling should start after the insert statement for Station_Point. If any of the inserts fails then the process should
stop and report an error message. Please help me with the error handling code. Let me know if you are not clear with my question or any clarifications..Thanks so much for your time..
