CREATE TABLE [Prescription].[Weight]
 (
    [SessionID]      INT  NOT NULL,
    [TargetWeight]  REAL NULL,
    [Intake]         REAL NULL,
    [WeightLoss]    REAL NULL,
    ....................................
);
ALTER TABLE [Prescription].[Weight]
    ADD CONSTRAINT [PK_Weight] PRIMARY KEY CLUSTERED ([SessionID] ASC);

CREATE TABLE [SessionData].[Weight] 
(
    [SessionID]         INT  NOT NULL,
    [Scale]              REAL NULL,
    [WeightLoss]       REAL NULL,
    ......................................
);
ALTER TABLE [SessionData].[Weight]
    ADD CONSTRAINT [PK_Weight] PRIMARY KEY CLUSTERED ([SessionID] ASC);

CREATE PROCEDURE [SessionData].[spGetWeight]
	@SessionID INT
AS
BEGIN
    SELECT  WO.SessionID,
            WO.Scale,
            WO.WeightLoss,
            WP.TargetWeight
    FROM [SessionData].[Weight] AS WO LEFT OUTER JOIN [Prescription].[Weight] WP ON(WO.SessionID = WP.SessionID)
    WHERE WO.SessionID = @SessionID;
END


CREATE PROCEDURE [Prescription].[spSaveWeight]
	@SessionID AS INT,
	@TargetWeight AS REAL = NULL,
	@Intake  AS REAL = NULL,
	@WeightLoss  AS REAL = NULL
AS
BEGIN
    BEGIN TRY
	BEGIN TRANSACTION;
		
        IF NOT EXISTS (SELECT 1 FROM [Prescription].[Weight] WHERE SessionID = @SessionID) 
        BEGIN        
		INSERT INTO [Prescription].[Weight]
			(SessionID,
			TargetWeight,
			Intake,
			WeightLoss
			)
		VALUES  (@SessionID,
			@TargetWeight,
			@Intake,
			@WeightLoss
			);
	END			
	ELSE
	BEGIN
		UPDATE [Prescription].[Weight]
		SET TargetWeight = @TargetWeight,
		    Intake = @Intake,
		    PreTara = @WeightLoss
		WHERE SessionID = @SessionID;
	END	
		
	IF NOT EXISTS (SELECT 1 FROM [SessionData].[Weight] WHERE SessionID = @SessionID) 
        BEGIN	        
		INSERT INTO [SessionData].[Weight] (SessionID, WeightLoss ) 
		VALUES (@SessionID, @WeightLoss );
	END				   
	ELSE
	BEGIN
		UPDATE [SessionData].[Weight]
		SET WeightLoss= @WeightLoss
		WHERE SessionID = @SessionID;
	END
	COMMIT TRANSACTION;
   END TRY
   BEGIN CATCH
      -- log error
   END CATCH
END