IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_UpdateMediactionFormsFromRDS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_UpdateMediactionFormsFromRDS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Vitacore (с)
-- Create date: 2015-07-29
-- Description: Загрузка справочника лекарственных форм
-- =============================================
CREATE PROCEDURE [dbo].[sp_UpdateMediactionFormsFromRDS]
    @Xml varchar(max)
    ,@QueueName varchar(100) = 'RDSSync'
    ,@dateTime datetime = null
AS
BEGIN
    SET NOCOUNT ON;

    declare @parsedXml xml

    select @dateTime = ISNULL(@dateTime, GETDATE())
            ,@parsedXml = cast(@Xml as xml)



    -- Получаем данные из XML
    select T.c.value('./Name[1]', 'varchar(255)') as Name
            ,T.c.value('./Code[1]', 'int') as Code
        INTO #medicationforms
    from @parsedXml.nodes('MEDICATION_FORMS/body/MEDICATION_FORMS/MEDICATION_FORM') T(c)



    -- Загружаем новые лекарственные формы
    INSERT INTO [PHARM].[T_MEDICATION_FORMS] ([Code], [Name])
    SELECT
               nmf.Code--<Code, int,>
               ,nmf.Name--<Name, varchar(255),>
    from #medicationforms nmf
    where not exists
        (
            select *
            from [PHARM].T_MEDICATION_FORMS mf
            where mf.Code = nmf.Code
        )



    -- Обновляем имеющиеся лекарственные формы
    update mf
    set mf.Name = nmf.Name--<Name, int,>
    from [PHARM].T_MEDICATION_FORMS mf
        join #medicationforms nmf on nmf.Code = mf.Code



    -- Drop Table
    DROP TABLE #medicationforms
END
