manualsp

manualsp.SQL

CREATE PROCEDURE dbo.usp_TrainandStoreModel
   @modelname nvarchar(32)
	,@modeltype nvarchar(32)
	,@descript nvarchar(128)

AS

set nocount on

BEGIN TRY
BEGIN TRAN
    -- This is to store the model that comes from the stored proc that 
	-- brings back a result set
	declare  @trained_model varbinary(max)
	
	-- Put the model into the table after building it
	exec [dbo].[buildCustomerTree] @trained_model_outer=@trained_model OUTPUT

	-- Did we already create a model like this one?
	declare @id int
	-- If so, get the PK for later use
	set @id = (select PredictiveModelId from dbo.PredictiveModel where ModelName = @modelname)

	-- If not, create the model record header and capture the PK value 
	-- from the IDENTITY column
	if (@id is null)
	BEGIN
		insert into dbo.PredictiveModel
		(
			 [ModelName]
			,[ModelType]
			,[ModelDescript]
		)
		values
		(
			 @ModelName 
			,@modeltype
			,@descript
		)
		set @id = @@identity
	END

	-- An now, we store the model + the descriptive metadata information
	INSERT INTO [dbo].[PredictiveModelDetail]
		(
		  [PredictiveModelId]
		 ,[Model]
		) 
	 values (@id,@trained_model)
COMMIT
END TRY
  BEGIN CATCH
    ROLLBACK;
  END CATCH;
go