Website.RecordColdRoomTemperatures
Documentation | WideWorldImporters |
Schema | Website |
Name | RecordColdRoomTemperatures |
Takes a sensor data list, as a table-valued parameter (TVP), and applies the data to the Warehouse.ColdRoomTemperatures temporal table.
Input/Output
Mode | Name | Data type | Description | |
---|---|---|---|---|
1 | IN | SensorReadings | table type |
Script
CREATE PROCEDURE Website.RecordColdRoomTemperatures @SensorReadings Website.SensorDataList READONLY WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English' ) BEGIN TRY DECLARE @NumberOfReadings int = (SELECT MAX(SensorDataListID) FROM @SensorReadings); DECLARE @Counter int = (SELECT MIN(SensorDataListID) FROM @SensorReadings); DECLARE @ColdRoomSensorNumber int; DECLARE @RecordedWhen datetime2(7); DECLARE @Temperature decimal(18,2); -- note that we cannot use a merge here because multiple readings might exist for each sensor WHILE @Counter <= @NumberOfReadings BEGIN SELECT @ColdRoomSensorNumber = ColdRoomSensorNumber, @RecordedWhen = RecordedWhen, @Temperature = Temperature FROM @SensorReadings WHERE SensorDataListID = @Counter; UPDATE Warehouse.ColdRoomTemperatures SET RecordedWhen = @RecordedWhen, Temperature = @Temperature WHERE ColdRoomSensorNumber = @ColdRoomSensorNumber; IF @@ROWCOUNT = 0 BEGIN INSERT Warehouse.ColdRoomTemperatures (ColdRoomSensorNumber, RecordedWhen, Temperature) VALUES (@ColdRoomSensorNumber, @RecordedWhen, @Temperature); END; SET @Counter += 1; END; END TRY BEGIN CATCH THROW 51000, N'Unable to apply the sensor data', 2; RETURN 1; END CATCH; END; |
Exported: 2019-02-04 23:04, Last imported: 2018-08-10 09:51