USE [WFMSDB]
GO
/****** Object:  StoredProcedure [dbo].[restUpdateWPMetadata]    Script Date: 2/27/2020 2:26:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[restUpdateWPMetadata]
  @strlData nvarchar(MAX)


AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

-- ============ Declarations section ============
-- Standard variables
DECLARE @l_vchProcname       varchar(35)
DECLARE @l_vchParmname       varchar(35)
DECLARE @l_vchTablename      varchar(255)
DECLARE @l_vchMessage        varchar(255)
DECLARE @l_intError          int
DECLARE @l_intRowcount       int
DECLARE @l_intRtrncode       int
DECLARE @l_intErrnum         int
DECLARE @l_intSqlstatus      int
DECLARE @l_intSuccess        int
DECLARE @l_intFail           int
DECLARE @l_intNTFND          int
DECLARE @l_dtmCurrentDate	 datetime
DECLARE @l_vchServerName	 varchar(255)

-------------------------------------------------
-- Local variables

-------------------------------------------------
-- Initialize standard variables
SELECT @l_vchProcname = OBJECT_NAME(@@procid)
SELECT @l_intError = 0, @l_intRtrncode = 0, @l_intRowcount = 0, @l_intSqlstatus = 0
SELECT @l_intSuccess = 0, @l_intFail = -1, @l_intNTFND = 100
SELECT @l_dtmCurrentDate = GETDATE()
SELECT @l_vchServerName = @@SERVERNAME

-------------------------------------------------
BEGIN TRY
-------------------------------------------------

BEGIN TRAN
------------------------------
--main Part
DECLARE @xmlData XML
SET @xmlData = CONVERT(XML, @strlData)

-- Get input data
SELECT
  x.value('Platform_ID[1]', 'smallint') AS Platform_ID,
  x.value('Platform_Name[1]', 'varchar(50)') AS Platform_Name,
  x.value('Property_Name[1]', 'varchar(50)') AS Property_Name,
  x.value('Property_Value[1]', 'varchar(50)') AS Property_Value,
  x.value('Property_Type[1]', 'smallint') AS Property_Type
INTO #newMetaProps
FROM @xmlData.nodes('//metaprops') XmlData(x)


-- Delete old metadata for platform
DELETE
	pmp
FROM
  PLATFORM_META_PROPS pmp
WHERE pmp.Platform_ID = (SELECT TOP(1)
  Platform_ID
FROM #newMetaProps)


-- Insert new metadata for platform
INSERT INTO PLATFORM_META_PROPS
  (
  Platform_ID,
  Platform_Name,
  Property_Name,
  Property_Value,
  Property_Type
  )
SELECT
  t.Platform_ID,
  t.Platform_Name,
  t.Property_Name,
  t.Property_Value,
  t.Property_Type
FROM
  #newMetaProps t


-- Drop temporary table
DROP TABLE #newMetaProps

SET @l_intRtrncode = 200

COMMIT TRAN

-------------------------------------------------
END TRY

BEGIN CATCH
    SELECT
  @l_intRtrncode = @l_intFail,
  @l_intErrnum = ERROR_NUMBER(),
  @l_vchMessage = ERROR_MESSAGE()

	SELECT
  @l_vchMessage = @l_vchProcname + ' error ' + CONVERT( varchar(9), @l_intErrnum ) + ': ' + @l_vchMessage

	IF @@TRANCOUNT > 0 -- Uncompleted transaction in the database
		ROLLBACK TRAN
INSERT INTO _log (StatusDescr)
VALUES(@l_vchMessage)
	RAISERROR (@l_vchMessage,16,1)

END CATCH

RETURN @l_intRtrncode