67 lines
1.8 KiB
Transact-SQL
67 lines
1.8 KiB
Transact-SQL
CREATE OR ALTER PROCEDURE pricing.sync_external
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON; -- causes runtime errors to auto-rollback
|
|
|
|
BEGIN TRY
|
|
|
|
BEGIN TRAN;
|
|
-- Refresh pricing.ffcret
|
|
TRUNCATE TABLE pricing.ffcret;
|
|
INSERT INTO pricing.ffcret
|
|
SELECT *
|
|
FROM fanalysis.rlarp.ffcret;
|
|
|
|
-- Refresh pricing.ffterr
|
|
TRUNCATE TABLE pricing.ffterr;
|
|
INSERT INTO pricing.ffterr
|
|
SELECT *
|
|
FROM fanalysis.rlarp.ffterr;
|
|
|
|
-- Refresh pricing.gld
|
|
TRUNCATE TABLE pricing.gld;
|
|
INSERT INTO pricing.gld
|
|
SELECT *
|
|
FROM fanalysis.rlarp.gld;
|
|
|
|
-- Refresh pricing.qrh
|
|
TRUNCATE TABLE pricing.qrh;
|
|
INSERT INTO pricing.qrh
|
|
SELECT *
|
|
FROM fanalysis.rlarp.qrh;
|
|
|
|
-- Refresh pricing.sach
|
|
TRUNCATE TABLE pricing.sach;
|
|
INSERT INTO pricing.sach
|
|
SELECT *
|
|
FROM fanalysis.lgdat.sach;
|
|
|
|
COMMIT TRAN;
|
|
|
|
-- rebuild last price
|
|
EXEC pricing.rebuild_lastprice;
|
|
|
|
-- rebuild ranged price list
|
|
EXEC pricing.rebuild_pricelist;
|
|
|
|
-- rebuild target prices
|
|
EXEC pricing.rebuild_targets;
|
|
|
|
END TRY
|
|
|
|
BEGIN CATCH
|
|
-- Rollback if any error
|
|
IF XACT_STATE() <> 0
|
|
ROLLBACK TRAN;
|
|
|
|
-- rethrow original error with context
|
|
DECLARE @ErrMsg NVARCHAR(4000) = ERROR_MESSAGE();
|
|
DECLARE @ErrSec NVARCHAR(200) = ERROR_SEVERITY();
|
|
DECLARE @ErrState INT = ERROR_STATE();
|
|
RAISERROR('refresh_pricing_tables failed: %s', 16, 1, @ErrMsg);
|
|
THROW; -- rethrow original error for callers to handle
|
|
END CATCH;
|
|
|
|
END
|