Website.RecordVehicleTemperature
Documentation | WideWorldImporters |
Schema | Website |
Name | RecordVehicleTemperature |
Takes a JSON array and uses it to update Warehouse.VehicleTemperatures.
Input/Output
Mode | Name | Data type | Description | |
---|---|---|---|---|
1 | IN | FullSensorDataArray | nvarchar(1000) |
Script
CREATE PROCEDURE Website.RecordVehicleTemperature @FullSensorDataArray nvarchar(1000) WITH EXECUTE AS OWNER AS BEGIN SET XACT_ABORT ON; DECLARE @CrLf nchar(2) = nchar(13) + nchar(10); DECLARE @HelpMessage nvarchar(max) = N'JSON sensor data is invalid. An example of what is required is as follows:' + @CrLf + @CrLf + N'{"Recordings":' + @CrLf + N' [' + @CrLf + N' {"type":"Feature", "geometry": {"type":"Point", "coordinates":[-89.7600464,50.4742420] }, "properties":{"rego":"WWI-321-A","sensor":1,"when":"2016-01-01T07:00:00","temp":3.96}},' + @CrLf + N' {"type":"Feature", "geometry": {"type":"Point", "coordinates":[-89.7600464,50.4742420] }, "properties":{"rego":"WWI-321-A","sensor":2,"when":"2016-01-01T07:00:00","temp":3.98}}' + @CrLf + N' ]' + @CrLf + N'}'; IF ISJSON(@FullSensorDataArray) = 0 BEGIN PRINT @HelpMessage; THROW 51000, N'FullSensorDataArray must be valid JSON data', 1; RETURN 1; END; BEGIN TRY BEGIN TRAN; INSERT Warehouse.VehicleTemperatures (VehicleRegistration, ChillerSensorNumber, RecordedWhen, Temperature, FullSensorData, IsCompressed, CompressedSensorData) SELECT VehicleRegistration, ChillerSensorNumber, RecordedWhen, Temperature, FullSensorData, 0, NULL FROM OPENJSON(@FullSensorDataArray, N'$.Recordings') WITH ( VehicleRegistration nvarchar(40) N'$.properties.rego', ChillerSensorNumber int N'$.properties.sensor', RecordedWhen datetime2(7) N'$.properties.when', Temperature decimal(18,2) N'$.properties.temp', FullSensorData nvarchar(max) N'$' AS JSON); IF @@ROWCOUNT = 0 BEGIN PRINT N'Warning: No valid sensor data found'; PRINT @HelpMessage; END; COMMIT; END TRY BEGIN CATCH PRINT @HelpMessage; THROW 51000, N'Valid JSON was supplied but does not match the temperature recordings array structure', 2; IF XACT_STATE() <> 0 ROLLBACK TRAN; RETURN 1; END CATCH; END; |
Exported: 2019-02-04 23:04, Last imported: 2018-08-10 09:51