--Create Model
CREATE PROCEDURE generate_linear_model
AS
BEGIN
    EXEC sp_execute_external_script
    @language = N'R'
    , @script = N'lrmodel <- rxLinMod(formula = mpg ~ weight, data = AutoData);
        trained_model <- data.frame(payload = as.raw(serialize(lrmodel, connection=NULL)));'
    , @input_data_1 = N'SELECT  mpg,weight FROM TutorialDB.dbo.Auto'
    , @input_data_1_name = N'AutoData'
    , @output_data_1_name = N'trained_model'
    WITH RESULT SETS ((model varbinary(max)));
END;
--Store Model
CREATE TABLE ML_Models (
    Model_ID int identity(1,1) primary key,
	model_name varchar(30) not null default('default model') ,
    model varbinary(max) not null);
INSERT INTO ML_Models (model)
EXEC generate_linear_model;
--Make Predictions based on the Model
DECLARE @MPGmodel varbinary(max) = (SELECT model FROM [dbo].[ML_Models] WHERE model_name = 'default model');
EXEC sp_execute_external_script
    @language = N'R'
    , @script = N'
            current_model <- unserialize(as.raw(MPGmodel));
            new <- data.frame(NewAutoData);
            predicted.mpg <- rxPredict(current_model, new);
            str(predicted.mpg);
            OutputDataSet <- cbind(new, ceiling(predicted.mpg));
            '
    , @input_data_1 = N' SELECT weight FROM [dbo].[AutoNew] '
    , @input_data_1_name = N'NewAutoData'
    , @params = N'@MPGmodel varbinary(max)'
    , @MPGmodel = @MPGmodel
WITH RESULT SETS (([new_weight] INT, [predicted_mpg] INT))